package com.app.business.service;

import com.app.business.calculate.CalculateUtils;
import com.app.business.en.HouseStatus;
import com.app.business.en.HouseType;
import com.app.business.en.Status;
import com.app.business.model.AzProject;
import com.app.business.model.Contract;
import com.app.business.model.House;
import com.app.business.model.StorageRoom;
import com.app.business.utils.UserPermissionUtil;
import com.app.system.jpa.JpaBaseDao;
import com.app.system.jpa.PageBean;
import com.app.system.jpa.Wrapper;
import com.app.system.permission.model.UserInfo;
import com.app.system.permission.service.DataDictService;
import com.app.system.permission.service.UserInfoService;
import com.app.system.utils.CollectionUtils;
import com.app.system.utils.ParamUtils;
import com.app.system.utils.WebUtils;
import com.app.system.utils.dataType.BigDecimalUtils;
import com.app.system.utils.dataType.DateUtils;
import com.app.system.utils.dataType.IntegerUtils;
import com.app.system.utils.dataType.StringUtils;
import com.app.system.utils.exception.Precondition;
import com.app.system.utils.exception.Response;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import org.apache.shiro.SecurityUtils;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.Query;
import javax.servlet.http.HttpServletRequest;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.util.*;

/**
 * Created by lsh on 2019/2/11.
 * 房源业务层
 *
 * @author lsh
 */
@SuppressWarnings(value = "ALL")
@Service
public class HouseService extends JpaBaseDao<House, Integer> {

    @Autowired
    private AzProjectService azProjectService;
    @Autowired
    private ContractService contractService;
    @Autowired
    private UserInfoService userInfoService;
    @Autowired
    private StorageRoomService storageRoomService;
    @Autowired
    private CarLotService carLotService;
    @Autowired
    private UserInfoForPropertyService userInfoForPropertyService;
    @Autowired
    private HouseBuyPersonService houseBuyPersonService;
    @Autowired
    private HousePersonForPropertyService housePersonForPropertyService;
    @Autowired
    private ActionHistoryService actionHistoryService;
    @Autowired
    private DataDictService dataDictService;
    @Autowired
    private ParameterService parameterService;
    String date_formater = "'%Y-%m-%d'";

    /**
     * 通过房源idList获取某些房源
     *
     * @param houseIdList
     * @return
     */
    public List<House> getHouseListByIds(String houseIdStr) {
        StringBuffer hql = new StringBuffer("from House h inner join fetch h.azProject azp where h.id in (" + houseIdStr + ") order by h.chooseHouseDate");
        List<House> houseList = entityManager.createQuery(hql.toString()).getResultList();
        return houseList;
    }

    /**
     * 查询某协议下所有的已选房源
     *
     * @param contractId
     * @return
     */
    public List<House> getHouseListByContractId(Integer contractId) {
        StringBuffer hql = new StringBuffer("from House h inner join fetch h.azProject azp where h.contract.id = :contractId order by h.chooseHouseDate");
        List<House> houseList = entityManager.createQuery(hql.toString()).setParameter("contractId", contractId).getResultList();
        return houseList;
    }

    /**
     * 契税计算使用，不可含商品房
     * @param contractId
     * @return
     */
    public List<House> getHouseListNoSpByContractId(Integer contractId) {
        StringBuffer hql = new StringBuffer("from House h inner join fetch h.azProject azp where h.contract.id = :contractId and azp.id not in (39) order by h.chooseHouseDate");
        List<House> houseList = entityManager.createQuery(hql.toString()).setParameter("contractId", contractId).getResultList();
        return houseList;
    }

    /**
     * 非商品房安置面积
     * @return
     */
    public BigDecimal getNoSpfArea(int contractId) {
        BigDecimal spfArea = BigDecimal.ZERO;
        StringBuffer sql = new StringBuffer("select SUM(IFNULL(h.property_area,0) + IFNULL(h.pool_area,0)) 'noSpfArea' ");
        sql.append(" from house h where h.az_project_id not in (39) and h.contract_id =:contractId ");
        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).
                setParameter("contractId",contractId)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        if (CollectionUtils.isNotEmpty(mapList)){

            spfArea = mapList.get(0).get("noSpfArea") == null ?
                    BigDecimal.ZERO
                    : BigDecimalUtils.stringToBigDecimal(mapList.get(0).get("noSpfArea").toString());
        }
        return spfArea;
    }

    /**
     * 查询某协议下所有的已选房源名称
     *
     * @param contractId
     * @return
     */
    public String getHouseListStrByContractId(Integer contractId) {
        StringBuffer hql = new StringBuffer("from House h inner join fetch h.azProject azp where h.contract.id = :contractId order by h.chooseHouseDate");
        List<House> houseList = entityManager.createQuery(hql.toString()).setParameter("contractId", contractId).getResultList();
        String houseStr = "";
        if (CollectionUtils.isNotEmpty(houseList)) {
            for (House house : houseList) {
                houseStr = houseStr + house.getAzProject().getProjectName() + house.getHouseNumStr() + "、";
            }
        }
        return houseStr.substring(0, houseStr.length() - 1);
    }

    /**
     * 查询某协议下所有的已选房源
     *
     * @param contractId
     * @return
     */
    public List<House> getHouseListByContractIdForArea(Integer contractId) {
        StringBuffer hql = new StringBuffer("from House h inner join fetch h.azProject azp where h.contract.id = :contractId order by h.houseStyle,(h.propertyArea+h.poolArea) desc");
        List<House> houseList = entityManager.createQuery(hql.toString()).setParameter("contractId", contractId).getResultList();
        return houseList;
    }

    /**
     * 查询某协议下所有的已选房源
     *
     * @param contractId
     * @return
     */
    public List<House> getHouseListByContractIdForArea2(Integer contractId) {
        StringBuffer hql = new StringBuffer("from House h inner join fetch h.azProject azp where h.contract.id = :contractId order by (h.propertyArea+h.poolArea)");
        List<House> houseList = entityManager.createQuery(hql.toString()).setParameter("contractId", contractId).getResultList();
        return houseList;
    }

    /**
     * 查询某协议下所有的已选期房房源
     *
     * @param contractId
     * @return
     */
    public List<House> getHouseListByContractIdAndHouseNature2(Integer contractId) {
        StringBuffer hql = new StringBuffer("from House h inner join fetch h.azProject azp where h.contract.id = :contractId and h.houseNature =2 order by h.chooseHouseDate");
        List<House> houseList = entityManager.createQuery(hql.toString()).setParameter("contractId", contractId).getResultList();
        return houseList;
    }

    /**
     * 查询某协议下所有的已选现房房源
     *
     * @param contractId
     * @return
     */
    public List<House> getHouseListByContractIdAndHouseNature1(Integer contractId) {
        StringBuffer hql = new StringBuffer("from House h inner join fetch h.azProject azp where h.contract.id = :contractId and h.houseNature =1 order by h.chooseHouseDate");
        List<House> houseList = entityManager.createQuery(hql.toString()).setParameter("contractId", contractId).getResultList();
        return houseList;
    }


    /**
     * 查询某协议下已缴款的已选房源
     *
     * @param contractId
     * @return
     */
    public List<House> getHandInMoneyHouseListByContractId(Integer contractId) {
        StringBuffer hql = new StringBuffer("from House h inner join fetch h.azProject azp where h.contract.id = :contractId and h.financeSettleStatus = 2 order by h.chooseHouseDate");
        List<House> houseList = entityManager.createQuery(hql.toString()).setParameter("contractId", contractId).getResultList();
        return houseList;
    }

    /**
     * 分页处理
     *
     * @return
     */
    public PageBean<House> houseListByProjectId(Integer projectId) {
        //查询语句
        StringBuffer query = new StringBuffer(" h from House h inner join fetch h.azProject p ");

        //默认排序(PageBean指定排序字段类型的话，则默认排序不使用)
        StringBuffer orderBy = new StringBuffer(" h.buildNum,h.houseNum");
        Wrapper wrapper = new Wrapper(query, orderBy);
        wrapper.initSearchParams();
        wrapper.andEq("p.id", projectId);
        return this.pageHql(wrapper);
    }

    /**
     * 分页处理
     *
     * @return
     */
    public PageBean<House> getHouseListByZsProjectIdAndBuildArea(Integer zsProjectId, BigDecimal buildArea) {
        List<Integer> houseIdList = this.getHouseIdListByZsProjectIdAndBuildArea(zsProjectId, buildArea);
        houseIdList.add(0);
        //查询语句
        StringBuffer query = new StringBuffer(" h from House h inner join fetch h.azProject p ");

        //默认排序(PageBean指定排序字段类型的话，则默认排序不使用)
        StringBuffer orderBy = new StringBuffer(" p.id,h.propertyArea+h.poolArea");
        Wrapper wrapper = new Wrapper(query, orderBy);
        wrapper.initSearchParams();
        wrapper.andIn("h.id", houseIdList);
        return this.pageHql(wrapper);
    }


    /**
     * 分页处理
     *
     * @return
     */
    public PageBean<House> getHouseExcelListByZsProjectIdAndBuildArea(Integer zsProjectId, BigDecimal buildArea) {
        List<Integer> houseIdList = this.getHouseIdListByZsProjectIdAndBuildArea(zsProjectId, buildArea);
        houseIdList.add(0);
        //查询语句
        StringBuffer query = new StringBuffer(" h from House h inner join fetch h.azProject p ");

        //默认排序(PageBean指定排序字段类型的话，则默认排序不使用)
        StringBuffer orderBy = new StringBuffer(" p.id,h.propertyArea+h.poolArea");
        PageBean pageBean = new PageBean();
        pageBean.setPageSize(Integer.MAX_VALUE);
        Wrapper wrapper = new Wrapper(pageBean, query, null, orderBy);
        wrapper.initSearchParams();
        wrapper.andIn("h.id", houseIdList);
        return this.pageHql(wrapper);
    }


    /**
     * 分页处理
     *
     * @return
     */
    public PageBean<House> houseExcelListByProjectId() {
        //查询语句
        StringBuffer query = new StringBuffer(" h from House h inner join fetch h.azProject p ");

        //默认排序(PageBean指定排序字段类型的话，则默认排序不使用)
        StringBuffer orderBy = new StringBuffer(" h.buildNum,h.houseNum");
        PageBean pageBean = new PageBean();
        pageBean.setPageSize(Integer.MAX_VALUE);
        Wrapper wrapper = new Wrapper(pageBean, query, null, orderBy);
        wrapper.initSearchParams();
        return this.pageHql(wrapper);
    }

    /**
     * 树形菜单所需要的
     *
     * @return
     */
    public List<Map> mapTreeData() {
        List<AzProject> azProjectList = this.azProjectService.azProjectList();
        //楼号集合Map<azProjectId,List<BuildName>>
        Map<Integer, List> buildMap = this.getAzProjectBuildMap();
        List<Map> result = Lists.newArrayList();
        this.childrenData(azProjectList, buildMap, result);
        return result;
    }

    /**
     * 征收项目房源查看的树形菜单所需要的
     * 根据zsProjectId得到所需数据
     *
     * @return
     */
    public List<Map> mapTreeDataForView(Integer zsProjectId) {
        List<String> azProjectIdList = this.getAzProjectIdListByZsProjectId(zsProjectId);
        List<AzProject> azProjectList = this.azProjectService.azProjectListByAzProjectIdList(azProjectIdList);
        //楼号集合Map<azProjectId,List<BuildName>>
        Map<Integer, List> buildMap = this.getAzProjectBuildMapByZsProject(zsProjectId);
        List<Map> result = Lists.newArrayList();
        this.childrenData(azProjectList, buildMap, result);
        return result;
    }


    /**
     * 封装树形菜单所需数据
     *
     * @param azProjectList
     * @param buildMap
     * @param result
     */
    private void childrenData(List<AzProject> azProjectList, Map<Integer, List> buildMap, List<Map> result) {
        if (CollectionUtils.isEmpty(azProjectList)) {
            return;
        }
        Map map = null;
        Map valueMap = null;
        List itemList = null;
        List<Map> childrenList = null;
        for (AzProject azProject : azProjectList) {
            map = null;
            map = Maps.newHashMap();
            map.put("id", null);
            map.put("name", azProject.getProjectName());
            map.put("spread", true);
            itemList = null;
            itemList = buildMap.get(azProject.getId());
            childrenList = new ArrayList<>();
            if (!CollectionUtils.isEmpty(itemList)) {
                for (Object o : itemList) {
                    valueMap = null;
                    valueMap = Maps.newHashMap();
                    valueMap.put("id", azProject.getId());
                    valueMap.put("name", o);
                    valueMap.put("spread", true);
                    childrenList.add(valueMap);
                }
            }
            map.put("children", childrenList);
            result.add(map);
        }
    }

    /**
     * 通过征收项目id获取所分配的安置项目id组成的list
     *
     * @param zsProjectId
     * @return
     */
    public List<String> getAzProjectIdListByZsProjectId(Integer zsProjectId) {
        StringBuffer sql = new StringBuffer("select distinct h.az_project_id as azProjectId from house h,zs_project_house zph ");
        sql.append(" where h.id = zph.house_id and zph.zs_project_id =:zsProjectId order by h.az_project_id");
        List<String> azProjectIdList = (List<String>) this.entityManager.createNativeQuery(sql.toString()).setParameter("zsProjectId", zsProjectId).getResultList();
        return azProjectIdList;
    }

    /**
     * 通过安置项目id，征收项目id获取下面分配对应的楼号
     *
     * @param zsProjectId
     * @return
     */
    public List<String> getBuildNumListByAzProjectId(Integer zsProjectId, Integer azProjectId) {
        StringBuffer sql = new StringBuffer("select distinct h.build_num as buildNum from house h,zs_project_house zph ");
        sql.append(" where h.id = zph.house_id and zph.zs_project_id =:zsProjectId and h.az_project_id =:azProjectId  ");
        sql.append(" ORDER BY CONVERT(SUBSTRING_INDEX(h.build_num,'-',1),SIGNED),CONVERT(SUBSTRING_INDEX(h.build_num,'-',-1),SIGNED) ");
        List<String> buildNumList = (List<String>) this.entityManager.createNativeQuery(sql.toString()).setParameter("zsProjectId", zsProjectId).setParameter("azProjectId", azProjectId).getResultList();
        return buildNumList;
    }

    /**
     * 根据协议中预选户型得到对应的楼栋list
     * @param zsProjectId
     * @param azProjectId
     * @param hxTypeList
     * @return
     */
    public List<String> getBuildNumListByAzProjectId(Integer zsProjectId, Integer azProjectId,List hxTypeList) {
        StringBuffer sql = new StringBuffer("select distinct h.build_num as buildNum from house h,zs_project_house zph ");
        sql.append(" where h.id = zph.house_id and zph.zs_project_id =:zsProjectId and h.az_project_id =:azProjectId  ");
        if (CollectionUtils.isNotEmpty(hxTypeList)) {
            String hxTypeListStr = hxTypeList.toString().replace("[","(").replace("]",")");
            sql.append(" and h.memo in " + hxTypeListStr + " ");
        }
        sql.append(" ORDER BY CONVERT(SUBSTRING_INDEX(h.build_num,'-',1),SIGNED),CONVERT(SUBSTRING_INDEX(h.build_num,'-',-1),SIGNED) ");
        List<String> buildNumList = (List<String>) this.entityManager.createNativeQuery(sql.toString()).setParameter("zsProjectId", zsProjectId).setParameter("azProjectId", azProjectId).getResultList();
        return buildNumList;
    }

    /**
     * 通过安置项目id获取下面对应的楼号
     *
     * @param
     * @return
     */
    public List<String> getBuildNumListByAzProjectId(Integer azProjectId) {
        StringBuffer sql = new StringBuffer("select distinct h.build_num as buildNum from house h ");
        sql.append(" where h.az_project_id =:azProjectId  ");
        sql.append(" ORDER BY CONVERT(SUBSTRING_INDEX(h.build_num,'-',1),SIGNED),CONVERT(SUBSTRING_INDEX(h.build_num,'-',-1),SIGNED) ");
        List<String> buildNumList = (List<String>) this.entityManager.createNativeQuery(sql.toString()).setParameter("azProjectId", azProjectId).getResultList();
        return buildNumList;
    }

    /**
     * 通过征收项目id得到所分配的房源id
     *
     * @param zsProjectId
     * @return
     */
    public List<Integer> getHouseIdListByZsProjectId(Integer zsProjectId) {
        String sql = "SELECT h.id FROM house h WHERE h.id in (SELECT DISTINCT zph.house_id FROM zs_project_house zph where zph.zs_project_id =:zsProjectId) ";
        Map<String, Object> map = Maps.newHashMap();
        map.put("zsProjectId", zsProjectId);
        return (List<Integer>) this.findSqlObject(sql, map);
    }

    /**
     * 通过征收项目id得到所分配的房源id
     *
     * @param zsProjectId
     * @return
     */
    public List<Integer> getHouseIdListByZsProjectIdAndBuildArea(Integer zsProjectId, BigDecimal buildArea) {
        String sql = "SELECT h.id FROM house h WHERE h.id in (SELECT DISTINCT zph.house_id FROM zs_project_house zph where zph.zs_project_id =:zsProjectId) ";
        if (buildArea.compareTo(BigDecimal.ZERO) > 0) {
            sql = sql + " and h.property_area+h.pool_area >= " + buildArea;
        }
        Map<String, Object> map = Maps.newHashMap();
        map.put("zsProjectId", zsProjectId);
        return (List<Integer>) this.findSqlObject(sql, map);
    }

    /**
     * 通过征收项目id得到所分配的房源id
     *
     * @param zsProjectId
     * @return
     */
    public List<Integer> getHouseIdListByZsProjectIdAndHouseStyle(Integer zsProjectId, String houseStyle) {
        String sql = "SELECT h.id FROM house h WHERE h.id in (SELECT DISTINCT zph.house_id FROM zs_project_house zph where zph.zs_project_id =:zsProjectId) AND h.hosue_style= :houseStyle ";
        Map<String, Object> map = Maps.newHashMap();
        map.put("zsProjectId", zsProjectId);
        map.put("houseStyle", houseStyle);
        return (List<Integer>) this.findSqlObject(sql, map);
    }


    /**
     * 通过征收项目id、是否保障房、房源面积等得到所分配的房源id
     *
     * @param zsProjectId
     * @return
     */
    public List<Integer> getHouseIdListByZsProjectIdAndHouseStyle(Integer zsProjectId, String houseStyle, BigDecimal area) {
        String sql = "SELECT h.id FROM house h WHERE h.id in (SELECT DISTINCT zph.house_id FROM zs_project_house zph where zph.zs_project_id =:zsProjectId) ";
        if (area != null) {
            sql = sql + " and (h.property_area+h.pool_area) > :area";
        }
        if (houseStyle != null && houseStyle.equals("已选择保障区房源")) {
            sql = sql + " or h.house_style = '保障区房源' ";
        }
        Map<String, Object> map = Maps.newHashMap();
        map.put("zsProjectId", zsProjectId);
        map.put("area", area);
        return (List<Integer>) this.findSqlObject(sql, map);
    }

    /**
     * 保障区不可选
     * 通过征收项目id、是否保障房、房源面积等得到所分配的房源id
     * 不可选房源Id
     *
     * @param zsProjectId
     * @return
     */
    public List<Integer> getBzHouseIdListByZsProjectIdAndHouseStyleAndArea(Integer zsProjectId, BigDecimal area, String houseHoldStr) {
        String sql = "SELECT h.id FROM house h WHERE h.id in (SELECT DISTINCT zph.house_id FROM zs_project_house zph where zph.zs_project_id =:zsProjectId) ";
        sql = sql + " and h.house_style = '保障房' ";
        //保障区为三房，则四房不可选
        if (houseHoldStr.equals("0302,")) {
            sql = sql + " and h.house_hold = '0402' ";
        } else if (houseHoldStr.equals("0402")) {
            //保障区为四房，则三房和超出区间的四房不可选
            sql = sql + " and ((h.house_hold = '0402' ";
            sql = sql + " and (h.property_area+h.pool_area)> '" + area + "' ) ";
            sql = sql + " or h.house_hold = '0302') ";
        } else if (houseHoldStr.equals("0302,0402")) {
            //保障区为三房、四房，则超出区间的四房不可选
            sql = sql + " and (h.house_hold = '0402' ";
            sql = sql + " and (h.property_area+h.pool_area)> '" + area + "' ) ";
        }
        Map<String, Object> map = Maps.newHashMap();
        map.put("zsProjectId", zsProjectId);
        return (List<Integer>) this.findSqlObject(sql, map);

    }

    /**
     * 普选区不可选
     * 通过征收项目id、是否保障房、房源面积等得到所分配的房源id
     * 不可选房源Id
     *
     * @param zsProjectId
     * @return
     */
    public List<Integer> getPxHouseIdListByZsProjectIdAndHouseStyleAndArea(Integer zsProjectId, BigDecimal area, String houseHoldStr) {
        String sql = "SELECT h.id FROM house h WHERE h.id in (SELECT DISTINCT zph.house_id FROM zs_project_house zph where zph.zs_project_id =:zsProjectId) ";
        sql = sql + " and h.house_style in  ('普选房','期房','保障转普选' )  ";

        if (StringUtils.isNotEmpty(houseHoldStr)) {
            if (houseHoldStr.equals("0202")) {
                sql = sql + " and ((h.house_hold = '0202' ";
                sql = sql + " and (h.property_area+h.pool_area) > '" + area + "' ) ";
                sql = sql + " or h.house_hold in ('0302','0402','0502','1000')) ";
            } else if (houseHoldStr.equals("0302")) {
                sql = sql + " and ((h.house_hold = '0302' ";
                sql = sql + " and (h.property_area+h.pool_area)> '" + area + "' ) ";
                sql = sql + " or h.house_hold in ('0402')) ";
            } else if (houseHoldStr.equals("0402")) {
                sql = sql + " and h.house_hold = '0402' ";
                sql = sql + " and (h.property_area+h.pool_area) > '" + area + "' ";
            } else {
                sql = sql + " and (h.property_area+h.pool_area) > '" + area + "'  ";
            }
        } else {
            sql = sql + " and (h.property_area+h.pool_area) > '" + area + "'  ";
        }
        Map<String, Object> map = Maps.newHashMap();
        map.put("zsProjectId", zsProjectId);
        return (List<Integer>) this.findSqlObject(sql, map);

    }


    /**
     * 得到Map<azprojectId,List<幢号>>
     *
     * @return
     */
    private Map<Integer, List> getAzProjectBuildMap() {
        //查询语句
        StringBuffer sql = new StringBuffer("select p.id,h.build_num from house h inner join az_project p on p.id = h.az_project_id group by p.id,h.build_num order by p.id,CONVERT(SUBSTRING_INDEX(h.build_num,'-',1),SIGNED),CONVERT(SUBSTRING_INDEX(h.build_num,'-',-1),SIGNED)");
        //默认排序(PageBean指定排序字段类型的话，则默认排序不使用)
        List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();

        Map<Integer, List> buildMap = Maps.newLinkedHashMap();
        if (CollectionUtils.isNotEmpty(list)) {
            for (Object o : list) {
                Object[] os = (Object[]) o;
                Integer azProjectId = Integer.valueOf(String.valueOf(os[0]));
                List builds = buildMap.get(azProjectId) == null ? new ArrayList() : buildMap.get(azProjectId);
                builds.add(os[1]);
                buildMap.put(azProjectId, builds);
            }
        }
        return buildMap;
    }


    /**
     * 根据zsProjectId得到所分配的安置项目楼号Map<azprojectId,List<幢号>>
     *
     * @return
     */
    private Map<Integer, List> getAzProjectBuildMapByZsProject(Integer zsProjectId) {
        //查询语句
        StringBuffer sql = new StringBuffer("select h.az_project_id,h.build_num from house h,zs_project_house zph ");
        sql.append(" where h.id = zph.house_id and zph.zs_project_id =:zsProjectId group by h.build_num order by h.az_project_id,CONVERT(SUBSTRING_INDEX(h.build_num,'-',1),SIGNED),CONVERT(SUBSTRING_INDEX(h.build_num,'-',-1),SIGNED)");
        List list = this.entityManager.createNativeQuery(sql.toString()).setParameter("zsProjectId", zsProjectId).getResultList();

        Map<Integer, List> buildMap = Maps.newLinkedHashMap();
        if (CollectionUtils.isNotEmpty(list)) {
            for (Object o : list) {
                Object[] os = (Object[]) o;
                Integer azProjectId = Integer.valueOf(String.valueOf(os[0]));
                List builds = buildMap.get(azProjectId) == null ? new ArrayList() : buildMap.get(azProjectId);
                builds.add(os[1]);
                buildMap.put(azProjectId, builds);
            }
        }
        return buildMap;
    }


    /**
     * 得到层号和当前楼层的List<House>对应的map
     *
     * @param houseList
     * @return
     */
    public Map<String, Map<String, House>> getFloorAndHouseNumMap(List<House> houseList) {
        Map<String, Map<String, House>> resultMap = Maps.newLinkedHashMap();
        Map<String, House> itemMap = Maps.newLinkedHashMap();
        String houseNumStr = null;
        if (CollectionUtils.isNotEmpty(houseList)) {
            for (House h : houseList) {
                String floor = String.valueOf(h.getFloor());
                itemMap = resultMap.get(floor) == null ? Maps.newLinkedHashMap() : resultMap.get(floor);
                houseNumStr = h.getHouseNum().substring(h.getHouseNum().length() - 2);
                IntegerUtils.stringToInteger(houseNumStr);
                itemMap.put(IntegerUtils.stringToInteger(houseNumStr).toString(), h);
                resultMap.put(floor, itemMap);
            }
        }
        return resultMap;
    }

    /**
     * 得到层号和当前楼层的List<House>对应的map
     *
     * @param houseList
     * @return
     */
    public Map<String, List<House>> getFloorHouseMap(List<House> houseList) {
        List<House> valueList = null;

        Map<String, List<House>> map = Maps.newLinkedHashMap();
        if (CollectionUtils.isNotEmpty(houseList)) {
            for (House h : houseList) {
                String floor = String.valueOf(h.getFloor());
                valueList = map.get(floor) == null ? new ArrayList<>() : map.get(floor);
                valueList.add(h);
                map.put(floor, valueList);
            }
        }
        return map;
    }

    /**
     * 得到层号和当前楼层的List<House>对应的map
     * 如果房源是预留则变为已选
     * 如果当前户不可选则改变为状态3
     *
     * @param houseList
     * @return
     */
    public Map<String, List<House>> getFloorHouseForHouseStatusMap
    (List<House> houseList, List<Integer> houseIdList, Contract contract) {
        List<House> valueList = null;

        Map<String, List<House>> map = Maps.newLinkedHashMap();
        if (CollectionUtils.isNotEmpty(houseList)) {
            for (House h : houseList) {
                String floor = String.valueOf(h.getFloor());
                valueList = map.get(floor) == null ? new ArrayList<>() : map.get(floor);
                if (h.getHouseStatus() != 1) {
                    h.setHouseStatus(2);
                }
                if (contract.getChooseLimitType() != null && contract.getChooseLimitType() != 1 && houseIdList.contains(h.getId()) && h.getHouseStatus() == 1) {
                    h.setHouseStatus(3);
                }
                valueList.add(h);
                map.put(floor, valueList);
            }
        }
        return map;
    }


    /**
     * 通过征收项目、安置项目、楼号得到所分配的houseList
     *
     * @param zsProjectId
     * @param azProjectId
     * @param buildNum
     * @return
     */
    public List<House> houseList(String zsProjectId, Integer azProjectId, String buildNum) {
        StringBuffer hql = new StringBuffer("from House h where 1=1 ");
        if (StringUtils.isNotEmpty(zsProjectId)) {
            hql.append(" and h.id in (select zph.houseId from ZsProjectHouse zph where zph.zsProjectId in (" + zsProjectId + ")) ");
        }
        hql.append(" and h.azProject.id = :azProjectId and h.buildNum = :buildNum ");
        hql.append(" order by h.floor,h.houseNum");
        Map<String, Object> map = Maps.newLinkedHashMap();
        map.put("azProjectId", azProjectId);
        map.put("buildNum", buildNum);
        return this.findHql(hql.toString(), map);
    }

    /**
     * 通过预选户型得到对应可选户型
     * @param zsProjectId
     * @param azProjectId
     * @param buildNum
     * @param hxTypeList
     * @return
     */
    public List<House> houseList(String zsProjectId, Integer azProjectId, String buildNum,List hxTypeList) {
        StringBuffer hql = new StringBuffer("from House h where 1=1 ");
        if (StringUtils.isNotEmpty(zsProjectId)) {
            hql.append(" and h.id in (select zph.houseId from ZsProjectHouse zph where zph.zsProjectId in (" + zsProjectId + ")) ");
        }
        hql.append(" and h.azProject.id = :azProjectId and h.buildNum = :buildNum ");
        if (CollectionUtils.isNotEmpty(hxTypeList)) {
            String hxTypeListStr = hxTypeList.toString().replace("[","(").replace("]",")");
            hql.append(" and h.memo in " + hxTypeListStr + " ");
        }
        hql.append(" order by h.floor,h.houseNum");
        Map<String, Object> map = Maps.newLinkedHashMap();
        map.put("azProjectId", azProjectId);
        map.put("buildNum", buildNum);
        return this.findHql(hql.toString(), map);
    }

    /**
     * 通过征收项目得到所分配的houseList
     *
     * @param zsProjectId
     * @return
     */
    public List<House> houseListByZsProjectId(String zsProjectId) {
        StringBuffer hql = new StringBuffer("from House h where  ");
        hql.append(" h.id in (select zph.houseId from ZsProjectHouse zph where zph.zsProjectId in (" + zsProjectId + ")) order by h.floor,h.houseNum");
        Map<String, Object> map = Maps.newLinkedHashMap();
        return this.findHql(hql.toString(), map);
    }

    /**
     * 得到houseList
     *
     * @param azProjectId
     * @param buildNum
     * @return
     */
    public List<House> houseList(Integer azProjectId, String buildNum) {
        StringBuffer sql = new StringBuffer("from House h where h.azProject.id = :azProjectId and h.buildNum = :buildNum order by h.floor,h.houseNum");
        Map<String, Object> map = Maps.newLinkedHashMap();
        map.put("azProjectId", azProjectId);
        map.put("buildNum", buildNum);
        return this.findHql(sql.toString(), map);
    }


    /**
     * 得到houseInfo by house_type    某个安置项目、楼号下个户型数量、面积统计
     *
     * @param azProjectId
     * @param buildNum
     * @return
     */
    public Map<String, Map> houseHoldInfoMap(Integer azProjectId, String buildNum) {
        StringBuffer sql = new StringBuffer(" SELECT h.house_hold as houseHold, ");
        sql.append(" IFNULL(COUNT(h.id),0) AS houseCountSum, ");
        sql.append(" IFNULL(SUM( IFNULL(h.property_area,0) + IFNULL(h.pool_area,0) ),0) AS buildAreaSum, ");
        sql.append(" IFNULL(SUM(CASE WHEN h.house_Status != 1 THEN 1 ELSE 0 END),0) AS  houseCountSumYes, ");
        sql.append(" IFNULL(SUM(CASE WHEN h.house_Status != 1 THEN IFNULL(h.property_area,0) + IFNULL(h.pool_area,0) ELSE 0 END),0) AS buildAreaYes, ");
        sql.append(" IFNULL(SUM(CASE WHEN h.house_Status = 1 THEN 1 ELSE 0 END),0) AS houseCountSumNo, ");
        sql.append(" IFNULL(SUM(CASE WHEN h.house_Status = 1 THEN IFNULL(h.property_area,0) + IFNULL(h.pool_area,0) ELSE 0 END),0) AS buildAreaNo ");
        sql.append(" FROM house h WHERE h.build_num =:buildNum AND h.az_project_id =:azProjectId GROUP BY h.house_hold ");
        Query query = this.entityManager.createNativeQuery(sql.toString()).setParameter("buildNum", buildNum).setParameter("azProjectId", azProjectId);
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> resultMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String houseType = map.get("houseHold").toString();
                resultMap.put(houseType, map);
            }
        }
        return resultMap;
    }

    /**
     * 财务结算表公维金展示
     * @param contractId
     * @return
     */
    public List<Map> getHouseGwjMap(Integer contractId) {
        StringBuffer sql = new StringBuffer("SELECT ");
        sql.append("h.house_nature AS 'houseNature',h.gwj_price AS 'gwjPrice', ");
        sql.append("SUM(IFNULL(h.property_area,0) + IFNULL(h.pool_area,0)) AS 'buildArea',SUM(IFNULL(h.gwj_money,0)) AS 'gwjMoney' ");
        sql.append("FROM house h WHERE h.contract_id =:contractId ");
        sql.append("GROUP BY h.house_nature,h.gwj_price ");
        Query query = this.entityManager.createNativeQuery(sql.toString()).setParameter("contractId", contractId);
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return mapList;
    }

    /**
     * 得到houseInfo by house_type    得到征收项目所分配的某个安置项目、楼号下某个户型数量、面积统计
     *
     * @param zsProjectId
     * @param azProjectId
     * @param buildNum
     * @return
     */
    public Map<String, Map> houseHoldInfoMap(Integer zsProjectId, Integer azProjectId, String buildNum) {
        StringBuffer sql = new StringBuffer(" SELECT h.house_hold as houseHold, ");
        sql.append(" IFNULL(COUNT(h.id),0) AS houseCount, ");
        sql.append(" IFNULL(SUM( IFNULL(h.property_area,0) + IFNULL(h.pool_area,0) ),0) AS houseAreaSum, ");
        sql.append(" IFNULL(SUM(CASE WHEN h.house_Status = 1 THEN 1 ELSE 0 END),0) AS houseCanChooseCount, ");
        sql.append(" IFNULL(SUM(CASE WHEN h.house_Status = 1 THEN IFNULL(h.property_area,0) + IFNULL(h.pool_area,0) ELSE 0 END),0) AS houseCanChooseAreaSum ");
        sql.append(" FROM house h WHERE h.build_num =:buildNum AND h.az_project_id =:azProjectId and h.id in (select zph.house_id from zs_project_house zph where zph.zs_project_id = :zsProjectId) GROUP BY h.house_hold ");

        Query query = this.entityManager.createNativeQuery(sql.toString()).setParameter("buildNum", buildNum).setParameter("azProjectId", azProjectId).setParameter("zsProjectId", zsProjectId);
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> resultMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String houseType = map.get("houseHold").toString();
                resultMap.put(houseType, map);
            }
        }
        return resultMap;
    }


    /**
     * 得到户型总和    某个项目、楼号下个户型数量、面积统计合计
     *
     * @param azProjectId
     * @param buildNum
     * @return
     */

    public Map houseHoldTotalMap(Integer azProjectId, String buildNum) {
        StringBuffer sql = new StringBuffer(" SELECT IFNULL(COUNT(h.id),0) AS houseCountSumTotal,");
        sql.append(" IFNULL(SUM( IFNULL(h.property_area,0) + IFNULL(h.pool_area,0) ),0) AS buildAreaSumTotal, ");
        sql.append(" IFNULL(SUM(CASE WHEN h.house_Status != 1 THEN 1 ELSE 0 END),0) AS  houseCountSumYesTotal, ");
        sql.append(" IFNULL(SUM(CASE WHEN h.house_Status != 1 THEN IFNULL(h.property_area,0) + IFNULL(h.pool_area,0) ELSE 0 END),0) AS buildAreaYesTotal, ");
        sql.append(" IFNULL(SUM(CASE WHEN h.house_Status = 1 THEN 1 ELSE 0 END),0) AS houseCountSumNoTotal, ");
        sql.append(" IFNULL(SUM(CASE WHEN h.house_Status = 1 THEN IFNULL(h.property_area,0) + IFNULL(h.pool_area,0) ELSE 0 END),0) AS buildAreaNoTotal ");
        sql.append(" FROM house h WHERE h.build_num =:buildNum AND h.az_project_id =:azProjectId ");
        Query query = this.entityManager.createNativeQuery(sql.toString()).setParameter("buildNum", buildNum).setParameter("azProjectId", azProjectId);
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map resultMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            resultMap = mapList.get(0);
        }
        return resultMap;
    }

    /**
     * 得到户型总和    得到征收项目下所分配的某个安置项目、楼号下的某个户型数量、面积统计合计
     *
     * @param zsProjectId
     * @param azProjectId
     * @param buildNum
     * @return
     */
    public Map houseHoldTotalMap(Integer zsProjectId, Integer azProjectId, String buildNum) {
        StringBuffer sql = new StringBuffer(" SELECT IFNULL(COUNT(h.id),0) AS houseTotalCount,");
        sql.append(" IFNULL(SUM( IFNULL(h.property_area,0) + IFNULL(h.pool_area,0) ),0) AS houseAreaTotal, ");
        sql.append(" IFNULL(SUM(CASE WHEN h.house_Status = 1 THEN 1 ELSE 0 END),0) AS  houseCanChooseCount, ");
        sql.append(" IFNULL(SUM(CASE WHEN h.house_Status = 1 THEN IFNULL(h.property_area,0) + IFNULL(h.pool_area,0) ELSE 0 END),0) AS houseCanChooseArea ");
        sql.append(" FROM house h WHERE h.build_num =:buildNum AND h.az_project_id =:azProjectId and h.id in (select zph.house_id from zs_project_house zph where zph.zs_project_id = :zsProjectId) ");
        Query query = this.entityManager.createNativeQuery(sql.toString()).setParameter("buildNum", buildNum).setParameter("azProjectId", azProjectId).setParameter("zsProjectId", zsProjectId);
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map resultMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            resultMap = mapList.get(0);
        }
        return resultMap;
    }


    /**
     * 得到楼层列表
     *
     * @param id
     * @param buildNum
     * @return
     */
    public List floorList(Integer id, String buildNum) {
        StringBuffer sql = new StringBuffer("select floor from house h where az_project_id = :azProjectId and build_num = :buildNum group by floor order by floor desc");
        Map<String, Object> map = Maps.newLinkedHashMap();
        map.put("azProjectId", id);
        map.put("buildNum", buildNum);
        List list = this.findSqlObject(sql.toString(), map);
        return list;
    }

    /**
     * 得到最大楼层以及最大房号
     *
     * @param azProjectId
     * @param buildNum
     * @return
     */
    public Map<String, Integer> getMaxFloorMap(Integer azProjectId, String buildNum) {
        Map<String, Integer> resultMap = Maps.newHashMap();
        StringBuffer sql = new StringBuffer("select ifnull(max(floor),0) as maxFloor,ifnull(MAX(RIGHT(house_num,2)),0) as maxHouseNum ");
        sql.append("from house h where az_project_id = :azProjectId and build_num = :buildNum");
        Query query = this.entityManager.createNativeQuery(sql.toString()).setParameter("azProjectId", azProjectId).setParameter("buildNum", buildNum);
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        if (CollectionUtils.isNotEmpty(mapList)) {
            Map map = mapList.get(0);
            Integer maxFloor = Integer.valueOf(map.get("maxFloor").toString());
            Integer maxHouseNum = Integer.valueOf(map.get("maxHouseNum").toString());
            resultMap.put("maxFloor", maxFloor);
            resultMap.put("maxHouseNum", maxHouseNum);
        }
        return resultMap;
    }

    /**
     * 得到某些征收项目所分配的房源的最大楼层以及最大房号
     *
     * @param azProjectId
     * @param buildNum
     * @return
     */
    public Map<String, Integer> getMaxFloorMap(String zsProjectIdStr, Integer azProjectId, String buildNum) {
        Map<String, Integer> resultMap = Maps.newHashMap();
        StringBuffer sql = new StringBuffer("select ifnull(max(h.floor),0) as maxFloor,ifnull(MAX(RIGHT(h.house_num,2)),0) as maxHouseNum ");
        sql.append("from house h where h.az_project_id = :azProjectId and h.build_num = :buildNum ");
        if (StringUtils.isNotEmpty(zsProjectIdStr)) {
            sql.append("and h.id in (select zp.house_id from zs_project_house zp where zp.zs_project_id in (" + zsProjectIdStr + ")) ");
        }
        Query query = this.entityManager.createNativeQuery(sql.toString()).setParameter("azProjectId", azProjectId).setParameter("buildNum", buildNum);
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        if (CollectionUtils.isNotEmpty(mapList)) {
            Map map = mapList.get(0);
            Integer maxFloor = Integer.valueOf(map.get("maxFloor").toString());
            Integer maxHouseNum = Integer.valueOf(map.get("maxHouseNum").toString());
            resultMap.put("maxFloor", maxFloor);
            resultMap.put("maxHouseNum", maxHouseNum);
        }
        return resultMap;
    }

    /**
     * 得到某些征收项目所分配的房源的最大楼层以及最大房号
     *
     * @param azProjectId
     * @param buildNum
     * @return
     */
    public Map<String, Integer> getMaxFloorMap(String zsProjectIdStr, Integer azProjectId, String buildNum, String floorList) {
        Map<String, Integer> resultMap = Maps.newHashMap();
        StringBuffer sql = new StringBuffer("select ifnull(max(h.floor),0) as maxFloor,ifnull(MAX(RIGHT(h.house_num,2)),0) as maxHouseNum ");
        sql.append("from house h where h.az_project_id = :azProjectId and h.build_num = :buildNum ");
        if (floorList != null && !floorList.equals("")) {
            sql.append(" and h.floor in (" + floorList + ") ");
        }
        if (StringUtils.isNotEmpty(zsProjectIdStr)) {
            sql.append("and h.id in (select zp.house_id from zs_project_house zp where zp.zs_project_id in (" + zsProjectIdStr + ")) ");
        }

        Query query = this.entityManager.createNativeQuery(sql.toString()).setParameter("azProjectId", azProjectId).setParameter("buildNum", buildNum);
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        if (CollectionUtils.isNotEmpty(mapList)) {
            Map map = mapList.get(0);
            Integer maxFloor = Integer.valueOf(map.get("maxFloor").toString());
            Integer maxHouseNum = Integer.valueOf(map.get("maxHouseNum").toString());
            resultMap.put("maxFloor", maxFloor);
            resultMap.put("maxHouseNum", maxHouseNum);
        }
        return resultMap;
    }

    /**
     * 安置项目得到房源数量(总计，已选)
     *
     * @param azProjectId
     * @return
     */
    public Map<String, Map> getHouseCountMap() {
        StringBuffer sql = new StringBuffer("select h.az_project_id as azProjectId,ifnull(COUNT(h.id),0) as houseTotal,");
        sql.append("ifnull(sum(case when h.house_status =2 then 1 else 0 end),0) as signHouse,");
        sql.append("ifnull(sum(case when h.take_house_status =2 then 1 else 0 end),0) as takeHouseCount");
        sql.append(" from house h group by h.az_project_id order by h.az_project_id");
        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> houseCountMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String azProjectId = map.get("azProjectId").toString();
                houseCountMap.put(azProjectId, map);
            }
        }
        return houseCountMap;
    }


    /**
     * 通过安置项目id获取所有 具有唯一性的 房源信息
     * Set<安置项目id-幢号-户号-所在层数>
     *
     * @param azProjectId
     * @return
     */
    public Set<String> findHouseSetByAzProjectId(Integer azProjectId) {
        StringBuffer hql = new StringBuffer("select CONCAT(h.az_project_id,'-',h.build_num,'-',h.house_num,'-',h.floor) from House h");
        hql.append(" where h.az_project_id =:azProjectId ");
        Map<String, Object> map = new LinkedHashMap<>();
        map.put("azProjectId", azProjectId);

        List<String> list = this.findSqlObject(hql.toString(), map);
        ;
        Set<String> resultSet = Sets.newHashSet();
        if (CollectionUtils.isNotEmpty(list)) {
            for (String houseString : list) {
                resultSet.add(houseString);
            }
        }
        return resultSet;
    }

    /**
     * 获取key值为<安置项目id-幢号-户号-所在层数>
     * 的house  Map
     *
     * @param azProjectId
     * @return
     */
    public Map<String, House> findHouseMapByAzProjectId() {
        Map<String, Object> map = Maps.newLinkedHashMap();
        StringBuffer sql = new StringBuffer("from House h inner join fetch h.azProject azp  ");

        List<House> houseList = this.findHql(sql.toString(), map);
        Map<String, House> houseMap = Maps.newHashMap();

        if (CollectionUtils.isNotEmpty(houseList)) {
            for (House house : houseList) {
                houseMap.put(house.getAzProject().getProjectName() + "-" + house.getBuildNum() + "-" + house.getHouseNum() + "-" + house.getFloor().toString(), house);
            }
        }
        return houseMap;
    }

    /**
     * 保存
     * 添加事务注解
     *
     * @param request
     * @return
     */
    @Transactional
    public synchronized Response chooseHouseSave(HttpServletRequest request) throws IllegalAccessException, NoSuchMethodException, InvocationTargetException {
        Integer contractId = ParamUtils.getInt(request, "contractId", 0);
        Integer houseId = ParamUtils.getInt(request, "houseId", 0);
        Integer displaceStatus = ParamUtils.getInt(request, "displaceStatus", 0);
        BigDecimal displaceArea = ParamUtils.getDecimal(request, "displaceArea", 0);
        House house = this.findOne(houseId);
        Contract contract = this.contractService.findOne(contractId);
        Precondition.checkAjaxArguement(house != null && contract != null, "1111", "数据错误！");

        Map map = Maps.newHashMap();
        map.put("zsProjectId", contract.getZsProject().getId());
        map.put("azProjectId", house.getAzProject().getId());
        map.put("buildNum", house.getBuildNum());
        map.put("contractId", contract.getId());
        Response response = new Response();
        response.setData(map);

        if (!(contract.getLimitStatus() != null && contract.getLimitStatus().equals(Status.NO.getIndex()))) {
            response.setCode("2222");
            response.setMessage("该家庭当前暂不能选择房源！");
            return response;
        } else if (!(contract.getChooseHouseStatus().equals(Status.NO.getIndex()))) {
            response.setCode("3333");
            response.setMessage("财务已审核通过，无法再变更房源！");
            return response;
        } else if (!(house.getHouseStatus().equals(HouseStatus.KEXUAN.getIndex()))) {
            response.setCode("4444");
            response.setMessage("该房源已被选！");
            return response;
        }

        //如果选择的房源是小户型房源
//        if (house.getHouseType() != null && house.getHouseType().equals(HouseType.SMALL.getIndex())) {
//            if (IntegerUtils.getNotNull(contract.getSmallHouseNum()) > 0) {
//                response.setCode("5555");
//                response.setMessage("一个选房号只能选择一套【小户型(单身公寓)】房源！");
//                return response;
//            }
//        }

        //如果选择根据比例进行置换或者申购的房源时保存
        if (house.getIsDisplace() == 2) {
            if (displaceStatus == 1) {
                //参与置换的应安置面积
                house.setDisplaceAreaForAzArea(BigDecimalUtils.getBigAndBigDiv(house.getBuildArea(), house.getDisplaceAreaBl(), 2));
                //进行置换的建筑面积
                house.setDisplaceArea(house.getBuildArea());
            } else if (displaceStatus == 2) {
                //进行申购的应安置面积
                house.setDisplaceAreaForAzArea(house.getBuildArea());
                //进行申购的建筑面积
                house.setBuyArea(house.getBuildArea());
                //进行申购的金额
                house.setBuyAreaMoney(BigDecimalUtils.getBigAndBigMul(house.getBuyArea(), house.getBuyPrice()));
            } else if (displaceStatus == 3) {
                //参与置换的应安置面积需要小于总可选房面积
                Precondition.checkAjaxArguement(displaceArea.compareTo(contract.getLimitHouseAreaTotal()) < 0, "1111", "输入的置换面积大于应安置面积，请核对！");
                //参与置换的应安置面积等于输入的面积
                house.setDisplaceAreaForAzArea(displaceArea);
                //参与置换的建筑面积等于置换的应安置面积乘以displaceAreaBl,并保留二位小数
                house.setDisplaceArea(BigDecimalUtils.getBigAndBigMul(displaceArea, house.getDisplaceAreaBl()).setScale(2, BigDecimal.ROUND_HALF_UP));
                //参与申购的建筑面积等于总建筑面积减去置换后的建筑面积
                house.setBuyArea(BigDecimalUtils.getBigAndBigSub(house.getBuildArea(), house.getDisplaceArea()));
                //参与申购的金额等于参与申购的建筑面积*单价
                house.setBuyAreaMoney(BigDecimalUtils.getBigAndBigMul(house.getBuyArea(), house.getBuyPrice()));

            }
            house.setDisplaceStatus(displaceStatus);
        }

        house.setHouseStatus(HouseStatus.YIXUAN.getIndex());   //已选
        house.setChooseHouseDate(new Date());   //选房时间
        house.setDoName(this.userInfoService.findByUsername(SecurityUtils.getSubject().getPrincipal().toString()).getName());   //操作人
        house.setContract(contract);
        this.save(house);

        if (contract.getZsProject().getProjectCode().equals("BQ001")) {
            List<House> houseList = this.getHouseListByContractIdForArea2(contractId);
            if (houseList.get(0) != null) {
                House house1 = houseList.get(0);
                if (house1.getAzProject().getId() == 4) {
                    contract.setParameter(this.parameterService.findOne(83));
                } else if (house1.getAzProject().getId() == 15) {
                    contract.setParameter(this.parameterService.findOne(82));

                }
            }
        }

        this.actionHistoryService.saveActionHistoryByContractId(contract.getId(), "选房房源" + house.getHouseNumStr(), "选择房源");

        CalculateUtils.main(contract.getId());
        response.setCode("0001");
        response.setMessage("保存成功！");
        return response;
    }


    /**
     * 保存
     * 添加事务注解
     *
     * @param request
     * @return
     */
    @Transactional
    public synchronized Response setDisplaceTypeSave(HttpServletRequest request) throws IllegalAccessException, NoSuchMethodException, InvocationTargetException {
        Integer contractId = ParamUtils.getInt(request, "contractId", 0);
        Integer houseId = ParamUtils.getInt(request, "houseId", 0);
        Integer displaceStatus = ParamUtils.getInt(request, "displaceStatus", 0);
        BigDecimal displaceArea = ParamUtils.getDecimal(request, "displaceArea", 0);
        House house = this.findOne(houseId);
        Contract contract = this.contractService.findOne(contractId);
        Precondition.checkAjaxArguement(house != null && contract != null, "1111", "数据错误！");

        Map map = Maps.newHashMap();
        map.put("contractId", contract.getId());
        Response response = new Response();
        response.setData(map);
        if ((contract.getChooseHouseStatus().equals(Status.YES.getIndex()))) {
            response.setCode("1111");
            response.setMessage("选房已签认，无法再修改！");
            return response;
        }
        //如果选择根据比例进行置换或者申购的房源时保存
        if (house.getIsDisplace() == 2) {
            if (displaceStatus == 1) {
                //参与置换的应安置面积
                house.setDisplaceAreaForAzArea(BigDecimalUtils.getBigAndBigDiv(house.getBuildArea(), house.getDisplaceAreaBl(), 2));
                //进行置换的建筑面积
                house.setDisplaceArea(house.getBuildArea());
                house.setBuyArea(BigDecimal.ZERO);
                house.setBuyAreaMoney(BigDecimal.ZERO);
            } else if (displaceStatus == 2) {
                house.setDisplaceAreaForAzArea(BigDecimal.ZERO);
                //进行申购的应安置面积
                house.setDisplaceArea(BigDecimal.ZERO);
                //进行申购的建筑面积
                house.setBuyArea(house.getBuildArea());
                //进行申购的金额
                house.setBuyAreaMoney(BigDecimalUtils.getBigAndBigMul(house.getBuyArea(), house.getBuyPrice()));
            } else if (displaceStatus == 3) {
                //参与置换的应安置面积需要小于总可选房面积
                Precondition.checkAjaxArguement(displaceArea.compareTo(contract.getLimitHouseAreaTotal()) < 0, "1111", "输入的置换面积大于应安置面积，请核对！");
                //参与置换的应安置面积等于输入的面积
                house.setDisplaceAreaForAzArea(displaceArea);
                //参与置换的建筑面积等于置换的应安置面积乘以displaceAreaBl,并保留二位小数
                house.setDisplaceArea(BigDecimalUtils.getBigAndBigMul(displaceArea, house.getDisplaceAreaBl()).setScale(2, BigDecimal.ROUND_HALF_UP));
                //参与申购的建筑面积等于总建筑面积减去置换后的建筑面积
                house.setBuyArea(BigDecimalUtils.getBigAndBigSub(house.getBuildArea(), house.getDisplaceArea()));
                //参与申购的金额等于参与申购的建筑面积*单价
                house.setBuyAreaMoney(BigDecimalUtils.getBigAndBigMul(house.getBuyArea(), house.getBuyPrice()));

            }
            house.setDisplaceStatus(displaceStatus);
        }

        this.save(house);

        this.actionHistoryService.saveActionHistoryByContractId(contract.getId(), "修改期房选房方式", "选择房源");

        CalculateUtils.main(contract.getId());
        response.setCode("0001");
        response.setMessage("保存成功！");
        return response;
    }


    /**
     * 选房撤销
     *
     * @param request
     * @return
     */
    @Transactional
    public synchronized Response houseDelete(HttpServletRequest request) throws IllegalAccessException, NoSuchMethodException, InvocationTargetException {
        Integer contractId = ParamUtils.getInt(request, "contractId", 0);
        Integer houseId = ParamUtils.getInt(request, "houseId", 0);
        House house = this.findOne(houseId);
        Contract contract = this.contractService.findOne(contractId);
        Precondition.checkAjaxArguement(house != null && contract != null, "1111", "数据错误！");
        Precondition.checkAjaxArguement(contract.getLimitStatus() != null && contract.getLimitStatus().equals(Status.NO.getIndex()), "2222", "该家庭当前暂不能撤销房源！");
        Precondition.checkAjaxArguement(contract.getAzSignStatus().equals(Status.TUI.getIndex()) || contract.getAzSignStatus().equals(Status.NO.getIndex()), "3333", "该协议已签署安置协议，不能撤销！");
        StorageRoom storageRoomOld = this.storageRoomService.getStorageRoomByHouseId(house.getId());
        Precondition.checkAjaxArguement(storageRoomOld == null, "4444", "该房源存在储藏室，请先撤销储藏室！");
        //如果撤销的是普通户型，需判断一下，撤销后，当前车位数量会不会大于限制数量
        if (house.getHouseType().equals(HouseType.NORMAL.getIndex())) {
            //撤销后车位最大限制数量
            Integer carLotLimitCount = this.carLotService.getCarLotLimitCount(IntegerUtils.getIntAndIntSub(contract.getNormalHouseNum(), 1));
            //注销车位验证信息
            //Precondition.checkAjaxArguement(IntegerUtils.getNotNull(contract.getCarLotNum()) <= carLotLimitCount, "5555", "该房源撤销后，您当前选择的车位数量将会超出限制【" + carLotLimitCount + "】个，请先撤销1个车位再撤销房源！");
        }
        Precondition.checkAjaxArguement(house.getHouseStatus().equals(HouseStatus.YIXUAN.getIndex()), "6666", "该房源已撤销，请勿重复操作！");

        //撤销选房时、删除全部关于选房置换、申购数据
        house.setDisplaceStatus(1);
        house.setDisplaceArea(BigDecimal.ZERO);
        house.setDisplaceAreaForAzArea(BigDecimal.ZERO);
        house.setBuyArea(BigDecimal.ZERO);
        house.setBuyAreaMoney(BigDecimal.ZERO);

        house.setHouseStatus(HouseStatus.KEXUAN.getIndex());   //可选
        house.setChooseHouseDate(null);   //选房时间
        house.setDoName(null);   //操作人
        house.setContract(null);
        this.save(house);
        if (contract.getZsProject().getProjectCode().equals("DY004")) {
            List<House> houseList = this.getHouseListByContractIdForArea2(contractId);
            if (CollectionUtils.isNotEmpty(houseList)) {
                if (houseList.get(0) != null) {
                    House house1 = houseList.get(0);
                    if (house1.getAzProject().getId() == 4) {
                        contract.setParameter(this.parameterService.findOne(72));
                        if (contract.getZsProject().getId() == 168) {
                            contract.setParameter(this.parameterService.findOne(74));
                        }
                    } else if (house1.getAzProject().getId() == 15) {
                        contract.setParameter(this.parameterService.findOne(71));
                        if (contract.getZsProject().getId() == 168) {
                            contract.setParameter(this.parameterService.findOne(73));
                        }
                    }
                }
            }
        }
        this.houseBuyPersonService.deleteHouseBuyPersonByHouseId(house.getId());//删除关联的购房人信息
        this.housePersonForPropertyService.deleteHousePersonForPropertyByHouseId(house.getId());//删除关联的物业减免人信息
        this.actionHistoryService.saveActionHistoryByContractId(contract.getId(), "撤销房源" + house.getHouseNumStr(), "撤销房源");

        CalculateUtils.main(contract.getId());

        BigDecimal chooseHouseArea = BigDecimalUtils.getNotNull(contract.getChooseHouseAreaTotal());
        return new Response("0001", "撤销成功！");
    }

    /**
     * 已选房源明细分页处理
     *
     * @return
     */
    public PageBean<House> getSelectedHousePageBean() {
        //查询语句
        StringBuffer query = new StringBuffer("h from House h inner join fetch h.contract c inner join fetch c.zsProject zsp inner join fetch h.azProject azp ");

        //默认排序(PageBean指定排序字段类型的话，则默认排序不使用)
        StringBuffer orderBy = new StringBuffer("azp.id,zsp.id,c.id,h.id");
        Wrapper wrapper = new Wrapper(query, orderBy);
        wrapper.initSearchParams();
        wrapper.andEq("h.houseStatus", Status.YES.getIndex());

        UserPermissionUtil.zsProjectUserLimit(wrapper);        //用户只能看到已经分配给自己的项目下的协议信息

        return this.pageHql(wrapper);
    }

    /**
     * 已选房源明细导出，不分页
     *
     * @return
     */
    public PageBean<House> getExcelSelectedHousePageBean() {
        //查询语句
        StringBuffer query = new StringBuffer("h from House h inner join fetch h.contract c inner join fetch c.zsProject zsp  inner join fetch h.azProject azp");

        //默认排序(PageBean指定排序字段类型的话，则默认排序不使用)
        StringBuffer orderBy = new StringBuffer("zsp.id,c.id,h.id");
        PageBean pageBean = new PageBean();
        pageBean.setPageSize(Integer.MAX_VALUE);
        Wrapper wrapper = new Wrapper(pageBean, query, null, orderBy);
        wrapper.andEq("h.houseStatus", Status.YES.getIndex());

        wrapper.initSearchParams();
        UserPermissionUtil.zsProjectUserLimit(wrapper);        //用户只能看到已经分配给自己的项目下的协议信息

        return this.pageHql(wrapper);
    }

    /**
     * 已选房源明细导出，不分页
     *
     * @return
     */
    public PageBean<House> getExcelSelectedHousePageBeanForProperty() {
        //查询语句
        StringBuffer query = new StringBuffer("h from House h inner join fetch h.contract c inner join fetch c.zsProject zsp  inner join fetch h.azProject azp");

        //默认排序(PageBean指定排序字段类型的话，则默认排序不使用)
        StringBuffer orderBy = new StringBuffer("zsp.id,c.id,h.id");
        PageBean pageBean = new PageBean();
        pageBean.setPageSize(Integer.MAX_VALUE);
        Wrapper wrapper = new Wrapper(pageBean, query, null, orderBy);
        wrapper.andEq("h.houseStatus", Status.YES.getIndex());
        wrapper.initSearchParams();
        return this.pageHql(wrapper);
    }

    /**
     * 得到某些征收项目下所分配的某些安置项目房源各楼房源选房情况
     *
     * @param zsProjectIdStr
     * @return
     */
    public Map<String, Map<String, Map<String, Integer>>> getChooseHouseCountMap(String zsProjectIdStr, String azProjectIdStr) {
        StringBuffer sql = new StringBuffer("SELECT h.az_project_id as azProjectId,h.build_num as buildNum,");
        sql.append("IFNULL(COUNT(h.id),0) AS houseCount,");
        sql.append("SUM(CASE WHEN h.house_type = '01' THEN 1 ELSE 0 END) AS normalHouseCount, "); //普通型
        sql.append("SUM(CASE WHEN h.house_type = '02' THEN 1 ELSE 0 END) AS smallHouseCount, "); //小户型
        sql.append("SUM(CASE WHEN h.house_type = '03' THEN 1 ELSE 0 END) AS fsHouseCount, "); //复式
        sql.append("SUM(CASE WHEN (h.house_type = '01' AND h.house_status = 1) THEN 1 ELSE 0 END) AS noNormalHouseCount,"); //普通型剩余
        sql.append("SUM(CASE WHEN (h.house_type = '02' AND h.house_status = 1) THEN 1 ELSE 0 END) AS noSmallHouseCount, "); //小户型剩余
        sql.append("SUM(CASE WHEN (h.house_type = '03' AND h.house_status = 1) THEN 1 ELSE 0 END) AS noFsHouseCount, "); //复式剩余

        sql.append("SUM(CASE WHEN (h.house_hold ='0502' OR h.house_hold ='1000') THEN 1 ELSE 0 END) AS bigHouseCount, "); //复式、五房总房源
        sql.append("SUM(CASE WHEN ((h.house_hold ='0502' OR h.house_hold ='1000') AND h.house_status = 1) THEN 1 ELSE 0 END) AS bigHouseNoChooseCount, "); //复式、五房剩余房源

        sql.append("SUM(CASE WHEN (h.house_hold ='0202' OR h.house_hold ='0302' OR h.house_hold ='0402') THEN 1 ELSE 0 END) AS noBigHouseCount, "); //非大户型总数量（二房、三房、四房）
        sql.append("SUM(CASE WHEN ((h.house_hold ='0202' OR h.house_hold ='0302' OR h.house_hold ='0402') AND h.house_status = 1) THEN 1 ELSE 0 END) AS noBigHouseNoChosoeCount, "); //非大户型剩余数量（二房、三房、四房）
        sql.append("SUM(CASE WHEN h.house_status <> 1 THEN 1 ELSE 0 END) AS chooseCount,");
        sql.append("SUM(CASE WHEN (h.house_status <> 1 AND DATE_FORMAT(h.choose_house_date,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')) THEN 1 ELSE 0 END) AS chooseCountCur,");
        sql.append("SUM(CASE WHEN h.house_status = 1 THEN 1 ELSE 0 END) AS noChooseCount ");
        sql.append("FROM house h WHERE h.id IN (SELECT DISTINCT zph.house_id FROM zs_project_house zph WHERE zph.zs_project_id IN (" + zsProjectIdStr + ")) ");
        sql.append("AND h.az_project_id in (" + azProjectIdStr + ") ");
        sql.append("GROUP BY h.az_project_id,h.build_num ORDER BY h.az_project_id,CONVERT(SUBSTRING_INDEX(h.build_num,'-',1),SIGNED),CONVERT(SUBSTRING_INDEX(h.build_num,'-',-1),SIGNED)");
        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map<String, Map<String, Integer>>> resultMap = Maps.newLinkedHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            Map<String, Map<String, Integer>> itemMap = Maps.newLinkedHashMap();
            Map<String, Integer> itemItemMap = Maps.newLinkedHashMap();
            for (Map map : mapList) {
                String azProjectId = map.get("azProjectId").toString();
                String buildNum = map.get("buildNum").toString();
                Integer houseCount = Integer.parseInt(map.get("houseCount").toString());
                Integer normalHouseCount = Integer.parseInt(map.get("normalHouseCount").toString());
                Integer smallHouseCount = Integer.parseInt(map.get("smallHouseCount").toString());
                Integer fsHouseCount = Integer.parseInt(map.get("fsHouseCount").toString());
                Integer noNormalHouseCount = Integer.parseInt(map.get("noNormalHouseCount").toString());
                Integer noSmallHouseCount = Integer.parseInt(map.get("noSmallHouseCount").toString());
                Integer noFsHouseCount = Integer.parseInt(map.get("noFsHouseCount").toString());
                Integer chooseCount = Integer.parseInt(map.get("chooseCount").toString());
                Integer chooseCountCur = Integer.parseInt(map.get("chooseCountCur").toString());
                Integer noChooseCount = Integer.parseInt(map.get("noChooseCount").toString());

                Integer bigHouseCount = Integer.parseInt(map.get("bigHouseCount").toString());
                Integer bigHouseNoChooseCount = Integer.parseInt(map.get("bigHouseNoChooseCount").toString());
                Integer noBigHouseCount = Integer.parseInt(map.get("noBigHouseCount").toString());
                Integer noBigHouseNoChosoeCount = Integer.parseInt(map.get("noBigHouseNoChosoeCount").toString());
                itemMap = CollectionUtils.isEmpty(resultMap.get(azProjectId)) ? Maps.newLinkedHashMap() : resultMap.get(azProjectId);
                itemItemMap = CollectionUtils.isEmpty(itemMap.get(buildNum)) ? Maps.newLinkedHashMap() : itemMap.get(buildNum);
                itemItemMap.put("houseCount", houseCount);
                itemItemMap.put("normalHouseCount", normalHouseCount);
                itemItemMap.put("smallHouseCount", smallHouseCount);
                itemItemMap.put("fsHouseCount", fsHouseCount);
                itemItemMap.put("noNormalHouseCount", noNormalHouseCount);
                itemItemMap.put("noSmallHouseCount", noSmallHouseCount);
                itemItemMap.put("noFsHouseCount", noFsHouseCount);
                itemItemMap.put("chooseCount", chooseCount);
                itemItemMap.put("chooseCountCur", chooseCountCur);
                itemItemMap.put("noChooseCount", noChooseCount);
                itemItemMap.put("bigHouseCount", bigHouseCount);
                itemItemMap.put("bigHouseNoChooseCount", bigHouseNoChooseCount);
                itemItemMap.put("noBigHouseCount", noBigHouseCount);
                itemItemMap.put("noBigHouseNoChosoeCount", noBigHouseNoChosoeCount);
                itemMap.put(buildNum, itemItemMap);
                resultMap.put(azProjectId, itemMap);
            }
        }
        return resultMap;
    }


    /**
     * 得到某些征收项目下所分配的某些安置项目房源各楼房源选房情况
     *
     * @param zsProjectIdStr
     * @return
     */
    public Map<String, Map<String, Map<String, Integer>>> getChooseHouseCountMapForBs(String zsProjectIdStr, String azProjectIdStr, String houseStyle) {
        StringBuffer sql = new StringBuffer("SELECT h.az_project_id as azProjectId,h.build_num as buildNum,");
        sql.append("IFNULL(COUNT(h.id),0) AS houseCount,");
        sql.append("SUM(CASE WHEN h.house_type = '01' THEN 1 ELSE 0 END) AS normalHouseCount, "); //普通型
        sql.append("SUM(CASE WHEN h.house_type = '02' THEN 1 ELSE 0 END) AS smallHouseCount, "); //小户型
        sql.append("SUM(CASE WHEN h.house_type = '03' THEN 1 ELSE 0 END) AS fsHouseCount, "); //复式
        sql.append("SUM(CASE WHEN (h.house_type = '01' AND h.house_status = 1) THEN 1 ELSE 0 END) AS noNormalHouseCount,"); //普通型剩余
        sql.append("SUM(CASE WHEN (h.house_type = '02' AND h.house_status = 1) THEN 1 ELSE 0 END) AS noSmallHouseCount, "); //小户型剩余
        sql.append("SUM(CASE WHEN (h.house_type = '03' AND h.house_status = 1) THEN 1 ELSE 0 END) AS noFsHouseCount, "); //复式剩余

        sql.append("SUM(CASE WHEN (h.house_hold ='0502' OR h.house_hold ='1000') THEN 1 ELSE 0 END) AS bigHouseCount, "); //复式、五房总房源
        sql.append("SUM(CASE WHEN ((h.house_hold ='0502' OR h.house_hold ='1000') AND h.house_status = 1) THEN 1 ELSE 0 END) AS bigHouseNoChooseCount, "); //复式、五房剩余房源

        sql.append("SUM(CASE WHEN (h.house_hold ='0202' OR h.house_hold ='0302' OR h.house_hold ='0402') THEN 1 ELSE 0 END) AS noBigHouseCount, "); //非大户型总数量（二房、三房、四房）
        sql.append("SUM(CASE WHEN ((h.house_hold ='0202' OR h.house_hold ='0302' OR h.house_hold ='0402') AND h.house_status = 1) THEN 1 ELSE 0 END) AS noBigHouseNoChosoeCount, "); //非大户型剩余数量（二房、三房、四房）
        sql.append("SUM(CASE WHEN h.house_status <> 1 THEN 1 ELSE 0 END) AS chooseCount,");
        sql.append("SUM(CASE WHEN (h.house_status <> 1 AND DATE_FORMAT(h.choose_house_date,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')) THEN 1 ELSE 0 END) AS chooseCountCur,");
        sql.append("SUM(CASE WHEN h.house_status = 1 THEN 1 ELSE 0 END) AS noChooseCount ");
        sql.append("FROM house h WHERE h.id IN (SELECT DISTINCT zph.house_id FROM zs_project_house zph WHERE zph.zs_project_id IN (" + zsProjectIdStr + ")) ");
        sql.append("AND h.az_project_id in (" + azProjectIdStr + ") ");
        if (StringUtils.isNotEmpty(houseStyle)) {
            sql.append("AND h.house_style = '" + houseStyle + "' ");
        }
        sql.append("GROUP BY h.az_project_id,h.build_num ORDER BY h.az_project_id,CONVERT(SUBSTRING_INDEX(h.build_num,'-',1),SIGNED),CONVERT(SUBSTRING_INDEX(h.build_num,'-',-1),SIGNED)");
        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map<String, Map<String, Integer>>> resultMap = Maps.newLinkedHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            Map<String, Map<String, Integer>> itemMap = Maps.newLinkedHashMap();
            Map<String, Integer> itemItemMap = Maps.newLinkedHashMap();
            for (Map map : mapList) {
                String azProjectId = map.get("azProjectId").toString();
                String buildNum = map.get("buildNum").toString();
                Integer houseCount = Integer.parseInt(map.get("houseCount").toString());
                Integer normalHouseCount = Integer.parseInt(map.get("normalHouseCount").toString());
                Integer smallHouseCount = Integer.parseInt(map.get("smallHouseCount").toString());
                Integer fsHouseCount = Integer.parseInt(map.get("fsHouseCount").toString());
                Integer noNormalHouseCount = Integer.parseInt(map.get("noNormalHouseCount").toString());
                Integer noSmallHouseCount = Integer.parseInt(map.get("noSmallHouseCount").toString());
                Integer noFsHouseCount = Integer.parseInt(map.get("noFsHouseCount").toString());
                Integer chooseCount = Integer.parseInt(map.get("chooseCount").toString());
                Integer chooseCountCur = Integer.parseInt(map.get("chooseCountCur").toString());
                Integer noChooseCount = Integer.parseInt(map.get("noChooseCount").toString());

                Integer bigHouseCount = Integer.parseInt(map.get("bigHouseCount").toString());
                Integer bigHouseNoChooseCount = Integer.parseInt(map.get("bigHouseNoChooseCount").toString());
                Integer noBigHouseCount = Integer.parseInt(map.get("noBigHouseCount").toString());
                Integer noBigHouseNoChosoeCount = Integer.parseInt(map.get("noBigHouseNoChosoeCount").toString());
                itemMap = CollectionUtils.isEmpty(resultMap.get(azProjectId)) ? Maps.newLinkedHashMap() : resultMap.get(azProjectId);
                itemItemMap = CollectionUtils.isEmpty(itemMap.get(buildNum)) ? Maps.newLinkedHashMap() : itemMap.get(buildNum);
                itemItemMap.put("houseCount", houseCount);
                itemItemMap.put("normalHouseCount", normalHouseCount);
                itemItemMap.put("smallHouseCount", smallHouseCount);
                itemItemMap.put("fsHouseCount", fsHouseCount);
                itemItemMap.put("noNormalHouseCount", noNormalHouseCount);
                itemItemMap.put("noSmallHouseCount", noSmallHouseCount);
                itemItemMap.put("noFsHouseCount", noFsHouseCount);
                itemItemMap.put("chooseCount", chooseCount);
                itemItemMap.put("chooseCountCur", chooseCountCur);
                itemItemMap.put("noChooseCount", noChooseCount);
                itemItemMap.put("bigHouseCount", bigHouseCount);
                itemItemMap.put("bigHouseNoChooseCount", bigHouseNoChooseCount);
                itemItemMap.put("noBigHouseCount", noBigHouseCount);
                itemItemMap.put("noBigHouseNoChosoeCount", noBigHouseNoChosoeCount);
                itemMap.put(buildNum, itemItemMap);
                resultMap.put(azProjectId, itemMap);
            }
        }
        return resultMap;
    }


    /**
     * 得到某些征收项目下所分配的某些安置项目房源各楼房源选房情况
     *
     * @param zsProjectIdStr
     * @return
     */
    public Map<String, Map<String, Map<String, Integer>>> getChooseHouseCountMapForXy(String zsProjectIdStr, String azProjectIdStr, String houseStyle, String memo) {
        StringBuffer sql = new StringBuffer("SELECT h.az_project_id as azProjectId,h.build_num as buildNum,");
        sql.append("IFNULL(COUNT(h.id),0) AS houseCount,");
        sql.append("SUM(CASE WHEN h.house_type = '01' THEN 1 ELSE 0 END) AS normalHouseCount, "); //普通型
        sql.append("SUM(CASE WHEN h.house_type = '02' THEN 1 ELSE 0 END) AS smallHouseCount, "); //小户型
        sql.append("SUM(CASE WHEN h.house_type = '03' THEN 1 ELSE 0 END) AS fsHouseCount, "); //复式
        sql.append("SUM(CASE WHEN (h.house_type = '01' AND h.house_status = 1) THEN 1 ELSE 0 END) AS noNormalHouseCount,"); //普通型剩余
        sql.append("SUM(CASE WHEN (h.house_type = '02' AND h.house_status = 1) THEN 1 ELSE 0 END) AS noSmallHouseCount, "); //小户型剩余
        sql.append("SUM(CASE WHEN (h.house_type = '03' AND h.house_status = 1) THEN 1 ELSE 0 END) AS noFsHouseCount, "); //复式剩余

        sql.append("SUM(CASE WHEN (h.house_hold ='0502' OR h.house_hold ='1000') THEN 1 ELSE 0 END) AS bigHouseCount, "); //复式、五房总房源
        sql.append("SUM(CASE WHEN ((h.house_hold ='0502' OR h.house_hold ='1000') AND h.house_status = 1) THEN 1 ELSE 0 END) AS bigHouseNoChooseCount, "); //复式、五房剩余房源

        sql.append("SUM(CASE WHEN (h.house_hold ='0202' OR h.house_hold ='0302' OR h.house_hold ='0402') THEN 1 ELSE 0 END) AS noBigHouseCount, "); //非大户型总数量（二房、三房、四房）
        sql.append("SUM(CASE WHEN ((h.house_hold ='0202' OR h.house_hold ='0302' OR h.house_hold ='0402') AND h.house_status = 1) THEN 1 ELSE 0 END) AS noBigHouseNoChosoeCount, "); //非大户型剩余数量（二房、三房、四房）
        sql.append("SUM(CASE WHEN h.house_status <> 1 THEN 1 ELSE 0 END) AS chooseCount,");
        sql.append("SUM(CASE WHEN (h.house_status <> 1 AND DATE_FORMAT(h.choose_house_date,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')) THEN 1 ELSE 0 END) AS chooseCountCur,");
        sql.append("SUM(CASE WHEN h.house_status = 1 THEN 1 ELSE 0 END) AS noChooseCount ");
        sql.append("FROM house h WHERE h.id IN (SELECT DISTINCT zph.house_id FROM zs_project_house zph WHERE zph.zs_project_id IN (" + zsProjectIdStr + ")) ");
        sql.append("AND h.az_project_id in (" + azProjectIdStr + ") ");
        if (StringUtils.isNotEmpty(houseStyle)) {
            sql.append("AND h.house_style = '" + houseStyle + "' ");
        }
        if (StringUtils.isNotEmpty(memo)) {
            sql.append("AND h.memo = '" + memo + "' ");
        }
        sql.append("GROUP BY h.az_project_id,h.build_num ORDER BY h.az_project_id,CONVERT(SUBSTRING_INDEX(h.build_num,'-',1),SIGNED),CONVERT(SUBSTRING_INDEX(h.build_num,'-',-1),SIGNED)");
        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map<String, Map<String, Integer>>> resultMap = Maps.newLinkedHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            Map<String, Map<String, Integer>> itemMap = Maps.newLinkedHashMap();
            Map<String, Integer> itemItemMap = Maps.newLinkedHashMap();
            for (Map map : mapList) {
                String azProjectId = map.get("azProjectId").toString();
                String buildNum = map.get("buildNum").toString();
                Integer houseCount = Integer.parseInt(map.get("houseCount").toString());
                Integer normalHouseCount = Integer.parseInt(map.get("normalHouseCount").toString());
                Integer smallHouseCount = Integer.parseInt(map.get("smallHouseCount").toString());
                Integer fsHouseCount = Integer.parseInt(map.get("fsHouseCount").toString());
                Integer noNormalHouseCount = Integer.parseInt(map.get("noNormalHouseCount").toString());
                Integer noSmallHouseCount = Integer.parseInt(map.get("noSmallHouseCount").toString());
                Integer noFsHouseCount = Integer.parseInt(map.get("noFsHouseCount").toString());
                Integer chooseCount = Integer.parseInt(map.get("chooseCount").toString());
                Integer chooseCountCur = Integer.parseInt(map.get("chooseCountCur").toString());
                Integer noChooseCount = Integer.parseInt(map.get("noChooseCount").toString());

                Integer bigHouseCount = Integer.parseInt(map.get("bigHouseCount").toString());
                Integer bigHouseNoChooseCount = Integer.parseInt(map.get("bigHouseNoChooseCount").toString());
                Integer noBigHouseCount = Integer.parseInt(map.get("noBigHouseCount").toString());
                Integer noBigHouseNoChosoeCount = Integer.parseInt(map.get("noBigHouseNoChosoeCount").toString());
                itemMap = CollectionUtils.isEmpty(resultMap.get(azProjectId)) ? Maps.newLinkedHashMap() : resultMap.get(azProjectId);
                itemItemMap = CollectionUtils.isEmpty(itemMap.get(buildNum)) ? Maps.newLinkedHashMap() : itemMap.get(buildNum);
                itemItemMap.put("houseCount", houseCount);
                itemItemMap.put("normalHouseCount", normalHouseCount);
                itemItemMap.put("smallHouseCount", smallHouseCount);
                itemItemMap.put("fsHouseCount", fsHouseCount);
                itemItemMap.put("noNormalHouseCount", noNormalHouseCount);
                itemItemMap.put("noSmallHouseCount", noSmallHouseCount);
                itemItemMap.put("noFsHouseCount", noFsHouseCount);
                itemItemMap.put("chooseCount", chooseCount);
                itemItemMap.put("chooseCountCur", chooseCountCur);
                itemItemMap.put("noChooseCount", noChooseCount);
                itemItemMap.put("bigHouseCount", bigHouseCount);
                itemItemMap.put("bigHouseNoChooseCount", bigHouseNoChooseCount);
                itemItemMap.put("noBigHouseCount", noBigHouseCount);
                itemItemMap.put("noBigHouseNoChosoeCount", noBigHouseNoChosoeCount);
                itemMap.put(buildNum, itemItemMap);
                resultMap.put(azProjectId, itemMap);
            }
        }
        return resultMap;
    }

    /**
     * 得到某些征收项目下所分配的安置项目id以及楼号
     *
     * @param zsProjectIdStr
     * @return
     */
    public List<String> getAzProjectIdBuildNumList(String zsProjectIdStr, String azProjectIdStr) {
        StringBuffer sql = new StringBuffer("SELECT CONCAT(h.az_project_id,'_',h.build_num) FROM house h WHERE h.id IN ");
        sql.append("(SELECT DISTINCT zph.house_id FROM zs_project_house zph WHERE zph.zs_project_id IN (" + zsProjectIdStr + ")) ");
        sql.append("AND h.az_project_id in (" + azProjectIdStr + ") ");
        sql.append("GROUP BY h.az_project_id,h.build_num ORDER BY h.az_project_id,CONVERT(SUBSTRING_INDEX(h.build_num,'-',1),SIGNED),CONVERT(SUBSTRING_INDEX(h.build_num,'-',-1),SIGNED)");
        List<String> azProjectIdBuildNumList = this.entityManager.createNativeQuery(sql.toString()).unwrap(org.hibernate.SQLQuery.class).list();
        return azProjectIdBuildNumList;
    }

    /**
     * 得到某些协议所对应的房源，并封装map
     *
     * @param contractIdList
     * @return
     */
    public Map<Integer, List<House>> getHouseMapByContractIdList(List<Integer> contractIdList) {
        StringBuffer hql = new StringBuffer("from House h inner join fetch h.contract c where c.id in (:contractIdList) order by h.buildNum,h.houseNum ");
        List<House> houseList = entityManager.createQuery(hql.toString()).setParameter("contractIdList", contractIdList).getResultList();
        Map<Integer, List<House>> resultMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(houseList)) {
            List<House> itemList = Lists.newLinkedList();
            for (House house : houseList) {
                itemList = null;
                itemList = resultMap.get(house.getContract().getId()) == null ? Lists.newLinkedList() : resultMap.get(house.getContract().getId());
                itemList.add(house);
                resultMap.put(house.getContract().getId(), itemList);
            }
        }
        return resultMap;
    }

    /**
     * 通过征收项目、安置项目、得到所分配的house的情况
     *
     * @param zsProjectId
     * @param azProjectId
     * @return
     */
    public Map<String, Map<String, Map<String, Map>>> getHouseHoldMap(String zsProjectIdStr, String azProjectStr) {
        Map<String, Map<String, Map<String, Map>>> houseHoldMap = Maps.newLinkedHashMap();
        StringBuffer sql = new StringBuffer("select h.az_project_id as azProjectId,h.build_num as buildNum,h.house_hold as houseHold,");
        sql.append("ifnull(count(h.id),0) as houseTotal,sum(ifnull(h.property_area,0)+ifnull(h.pool_area,0)) as houseAreaTotal,");
        sql.append("sum(case when h.house_status !=1 then 1 else 0 end) as chooseHouseTotal,");
        sql.append("sum(case when h.house_status !=1 then (ifnull(h.property_area,0)+ifnull(h.pool_area,0)) else 0 end) as chooseHouseAreaTotal,");
        sql.append("sum(case when h.house_status =1 then 1 else 0 end) as unChooseHouseTotal,");
        sql.append("sum(case when h.house_status =1 then (ifnull(h.property_area,0)+ifnull(h.pool_area,0)) else 0 end) as unChooseHouseAreaTotal ");
        sql.append("from house h where h.az_project_id in (" + azProjectStr + ") and h.id in ");
        sql.append("(select zph.house_id from zs_project_house zph where zph.zs_project_id in (" + zsProjectIdStr + ")) ");
        sql.append("GROUP BY h.az_project_id,h.build_num,h.house_hold ");
        sql.append("order by h.az_project_id,CONVERT(SUBSTRING_INDEX(h.build_num,'-',1),SIGNED),CONVERT(SUBSTRING_INDEX(h.build_num,'-',-1),SIGNED),h.house_hold");
        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        if (CollectionUtils.isNotEmpty(mapList)) {
            Map<String, Map<String, Map>> itemMap = Maps.newLinkedHashMap();
            Map<String, Map> itemItemMap = Maps.newLinkedHashMap();
            for (Map map : mapList) {
                String azProjectId = map.get("azProjectId").toString();
                String buildNum = map.get("buildNum").toString();
                String houseHold = map.get("houseHold").toString();
                itemMap = houseHoldMap.get(azProjectId) == null ? Maps.newLinkedHashMap() : houseHoldMap.get(azProjectId);
                itemItemMap = itemMap.get(buildNum) == null ? Maps.newLinkedHashMap() : itemMap.get(buildNum);
                itemItemMap.put(houseHold, map);
                itemMap.put(buildNum, itemItemMap);
                houseHoldMap.put(azProjectId, itemMap);
            }
        }
        return houseHoldMap;
    }


    /**
     * 通过征收项目、安置项目、得到所分配的house的户型情况
     *
     * @param zsProjectId
     * @param azProjectId
     * @return
     */
    public Map<String, Map<String, Map>> getHouseHoldForBsMap(String zsProjectIdStr, String azProjectStr) {
        Map<String, Map<String, Map>> houseHoldMap = Maps.newLinkedHashMap();
        StringBuffer sql = new StringBuffer("select h.az_project_id as azProjectId,h.house_hold as houseHold,");
        sql.append("ifnull(count(h.id),0) as houseTotal,sum(ifnull(h.property_area,0)+ifnull(h.pool_area,0)) as houseAreaTotal,");
        sql.append("sum(case when h.house_status !=1 then 1 else 0 end) as chooseHouseTotal,");
        sql.append("sum(case when h.house_status !=1 then (ifnull(h.property_area,0)+ifnull(h.pool_area,0)) else 0 end) as chooseHouseAreaTotal,");
        sql.append("sum(case when h.house_status =1 then 1 else 0 end) as unChooseHouseTotal,");
        sql.append("sum(case when h.house_status =1 then (ifnull(h.property_area,0)+ifnull(h.pool_area,0)) else 0 end) as unChooseHouseAreaTotal ");
        sql.append("from house h where h.az_project_id in (" + azProjectStr + ") and h.id in ");
        sql.append("(select zph.house_id from zs_project_house zph where zph.zs_project_id in (" + zsProjectIdStr + ")) ");
        sql.append("GROUP BY h.az_project_id,h.house_hold ");
        sql.append("order by h.az_project_id,h.house_hold");
        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        if (CollectionUtils.isNotEmpty(mapList)) {
            Map<String, Map> itemMap = Maps.newLinkedHashMap();
            for (Map map : mapList) {
                String azProjectId = map.get("azProjectId").toString();
                String houseHold = map.get("houseHold").toString();

                itemMap = houseHoldMap.get(azProjectId) == null ? Maps.newLinkedHashMap() : houseHoldMap.get(azProjectId);
                itemMap.put(houseHold, map);
                houseHoldMap.put(azProjectId, itemMap);
            }
        }
        return houseHoldMap;
    }

    /**
     * 通过征收项目、安置项目、得到所分配的house的户型情况
     *
     * @param zsProjectId
     * @param azProjectId
     * @return
     */
    public Map<String, Map<String, Map>> getHouseHoldForYlMap(String zsProjectIdStr, String azProjectStr, String houseStyle) {
        Map<String, Map<String, Map>> houseHoldMap = Maps.newLinkedHashMap();
        StringBuffer sql = new StringBuffer("select h.az_project_id as azProjectId,h.house_hold as houseHold,");
        sql.append("ifnull(count(h.id),0) as houseTotal,sum(ifnull(h.property_area,0)+ifnull(h.pool_area,0)) as houseAreaTotal,");
        sql.append("sum(case when h.house_status !=1 then 1 else 0 end) as chooseHouseTotal,");
        sql.append("sum(case when h.house_status !=1 then (ifnull(h.property_area,0)+ifnull(h.pool_area,0)) else 0 end) as chooseHouseAreaTotal,");
        sql.append("sum(case when h.house_status =1 then 1 else 0 end) as unChooseHouseTotal,");
        sql.append("sum(case when h.house_status =1 then (ifnull(h.property_area,0)+ifnull(h.pool_area,0)) else 0 end) as unChooseHouseAreaTotal ");
        sql.append("from house h where h.az_project_id in (" + azProjectStr + ") and h.id in ");
        sql.append("(select zph.house_id from zs_project_house zph where zph.zs_project_id in (" + zsProjectIdStr + ")) ");
        if (StringUtils.isNotEmpty(houseStyle)) {
            sql.append(" and h.memo = '" + houseStyle + "'");
        }
        sql.append("GROUP BY h.az_project_id,h.house_hold ");
        sql.append("order by h.az_project_id,h.house_hold");
        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        if (CollectionUtils.isNotEmpty(mapList)) {
            Map<String, Map> itemMap = Maps.newLinkedHashMap();
            for (Map map : mapList) {
                String azProjectId = map.get("azProjectId").toString();
                String houseHold = map.get("houseHold").toString();

                itemMap = houseHoldMap.get(azProjectId) == null ? Maps.newLinkedHashMap() : houseHoldMap.get(azProjectId);
                itemMap.put(houseHold, map);
                houseHoldMap.put(azProjectId, itemMap);
            }
        }
        return houseHoldMap;
    }

    /**
     * 通过征收项目、安置项目、得到所分配的house的户型情况
     *
     * @param zsProjectId
     * @param azProjectId
     * @return
     */
    public Map<String, Map<String, Map>> getHouseHoldForXyMap(String zsProjectIdStr, String azProjectStr, String houseStyle, String memo) {
        Map<String, Map<String, Map>> houseHoldMap = Maps.newLinkedHashMap();
        StringBuffer sql = new StringBuffer("select h.az_project_id as azProjectId,h.memo as houseHold,");
        sql.append("ifnull(count(h.id),0) as houseTotal,sum(ifnull(h.property_area,0)+ifnull(h.pool_area,0)) as houseAreaTotal,");
        sql.append("sum(case when h.house_status !=1 then 1 else 0 end) as chooseHouseTotal,");
        sql.append("sum(case when h.house_status !=1 then (ifnull(h.property_area,0)+ifnull(h.pool_area,0)) else 0 end) as chooseHouseAreaTotal,");
        sql.append("sum(case when h.house_status =1 then 1 else 0 end) as unChooseHouseTotal,");
        sql.append("sum(case when h.house_status =1 then (ifnull(h.property_area,0)+ifnull(h.pool_area,0)) else 0 end) as unChooseHouseAreaTotal ");
        sql.append("from house h where h.az_project_id in (" + azProjectStr + ") and h.id in ");
        sql.append("(select zph.house_id from zs_project_house zph where zph.zs_project_id in (" + zsProjectIdStr + ")) ");
        if (StringUtils.isNotEmpty(houseStyle)) {
            sql.append(" and h.house_style = '" + houseStyle + "'");
        }
        /*if (StringUtils.isNotEmpty(memo)) {
            sql.append(" and h.memo = '" + memo + "'");
        }*/
        sql.append("GROUP BY h.az_project_id,h.memo ");
        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        if (CollectionUtils.isNotEmpty(mapList)) {
            Map<String, Map> itemMap = Maps.newLinkedHashMap();
            for (Map map : mapList) {
                String azProjectId = map.get("azProjectId").toString();
                String houseHold = map.get("houseHold").toString();

                itemMap = houseHoldMap.get(azProjectId) == null ? Maps.newLinkedHashMap() : houseHoldMap.get(azProjectId);
                itemMap.put(houseHold, map);
                houseHoldMap.put(azProjectId, itemMap);
            }
        }
        return houseHoldMap;
    }

    /**
     * 得到协议id不为空的房源map
     *
     * @return
     */
    public Map<String, List<House>> getHouseByContractIdMap() {
        StringBuffer hql = new StringBuffer("from House h inner join fetch h.azProject azp where h.contract.id is not null order by h.chooseHouseDate");
        List<House> houseList = entityManager.createQuery(hql.toString()).getResultList();

        List<House> valueList = null;

        Map<String, List<House>> map = Maps.newLinkedHashMap();
        if (CollectionUtils.isNotEmpty(houseList)) {
            for (House h : houseList) {
                String id = String.valueOf(h.getContract().getId());
                valueList = map.get(id) == null ? new ArrayList<>() : map.get(id);
                valueList.add(h);
                map.put(id, valueList);
            }
        }
        return map;
    }


    /**
     * 设置房源结算状态（可撤销）
     *
     * @param house
     * @return
     */
    @Transactional
    public void setHouseFinanceSettleStatus(House house) throws IllegalAccessException, NoSuchMethodException, InvocationTargetException {
        if (house.getFinanceSettleStatus().equals(Status.NO.getIndex())) {
            house.setFinanceSettleStatus(Status.YES.getIndex());
            house.setFinanceSettleDate(new Date());
        } else {
            house.setFinanceSettleStatus(Status.NO.getIndex());
            house.setFinanceSettleDate(null);
        }
        this.save(house);
    }


    /**
     * 得到houseList
     *
     * @param azProjectId
     * @param buildNum
     * @return
     */
    public List<House> houseList(String town) {
        StringBuffer sql = new StringBuffer("from House h inner join azProject azp on h.azProjectId=azp.id where azp.towm=:town order by azp.id,h.houseNum");
        Map<String, Object> map = Maps.newLinkedHashMap();
        map.put("town", town);
        return this.findHql(sql.toString(), map);
    }


    public String getAzProjectIdAndBuildNum() {
        UserInfo userInfo = WebUtils.getLoginUserInfo();

        List<String> azProjectIdBuildNumList = this.userInfoForPropertyService.findByUserIdAndTowm(userInfo.getId());
        String ids = "'0',";
        if (CollectionUtils.isNotEmpty(azProjectIdBuildNumList)) {
            for (String s : azProjectIdBuildNumList) {
                if (azProjectIdBuildNumList.size() - 1 == azProjectIdBuildNumList.indexOf(s)) {
                    ids = ids + "'" + s + "'";
                } else {
                    ids = ids + "'" + s + "',";
                }
            }
        }
        return ids;
    }


    /**
     * 交房列表//所分配权限内档案已发放房源
     *
     * @return
     */
    public PageBean<House> getAzStutasHousePageBean() {

        String ids = this.getAzProjectIdAndBuildNum();
        //查询语句
        StringBuffer query = new StringBuffer("h from House h inner join fetch  h.azProject azp inner join fetch h.contract c ");
        query.append(" where  CONCAT(azp.id,'_',h.buildNum) in (");
        query.append(ids);
        query.append(" ) ");

        StringBuffer orderBy = new StringBuffer("azp.id,h.buildNum,h.houseNum");
        Wrapper wrapper = new Wrapper(query, orderBy);
        wrapper.initSearchParams();
        wrapper.andNewEq("h.houseStatus", Status.YES.getIndex());
        return this.pageHql(wrapper);
    }


    /**
     * 交房列表//所分配权限内档案已发放房源
     *
     * @return
     */
    public PageBean<House> getTakeHousePageBean() {

        String ids = this.getAzProjectIdAndBuildNum();
        //查询语句
        StringBuffer query = new StringBuffer("h from House h inner join fetch  h.azProject azp inner join fetch h.contract c ");
        query.append(" where  CONCAT(azp.id,'_',h.buildNum) in (");
        query.append(ids);
        query.append(") ");

        StringBuffer orderBy = new StringBuffer("c.id,h.takeHouseStatus,azp.id,h.buildNum,h.houseNum");
        Wrapper wrapper = new Wrapper(query, orderBy);
        wrapper.initSearchParams();
        wrapper.andNewEq("h.houseStatus", Status.YES.getIndex());
        return this.pageHql(wrapper);
    }

    /**
     * 物业交房列表//所分配权限内档案已发放房源导出
     *
     * @return
     */
    public PageBean<House> getTakeHouseExcelPageBean() {

        String ids = this.getAzProjectIdAndBuildNum();
        //查询语句
        StringBuffer query = new StringBuffer("h from House h inner join fetch  h.azProject azp inner join fetch h.contract c ");
        query.append(" where  CONCAT(azp.id,'_',h.buildNum) in (");
        query.append(ids);
        query.append(") ");

        StringBuffer orderBy = new StringBuffer("h.takeHouseStatus,azp.id,h.buildNum,h.houseNum");
        PageBean pageBean = new PageBean();
        pageBean.setPageSize(Integer.MAX_VALUE);
        Wrapper wrapper = new Wrapper(pageBean, query, null, orderBy);
        wrapper.initSearchParams();
        wrapper.andNewEq("h.houseStatus", Status.YES.getIndex());
        return this.pageHql(wrapper);
    }

    /**
     * 得到协议下所对应状态的houseList
     *
     * @param azProjectId
     * @param buildNum
     * @return
     */
    public List<House> houseListByStatus(Integer contractId, String status) {

        Map<String, Object> map = Maps.newLinkedHashMap();
        StringBuffer sql = new StringBuffer("from House h inner join fetch h.azProject azp where h.contract.id = :contractId  ");
        map.put("contractId", contractId);
        if (status.equals("houseStatus")) {
            sql.append(" order by h.houseStatus desc,h.chooseHouseDate");
        } else if (status.equals("financeSettleStatus")) {
            sql.append(" order by h.financeSettleStatus desc,h.financeSettleDate");
        } else if (status.equals("takeHouseStatus")) {
            sql.append(" order by h.takeHouseStatus desc,h.takeHouseDate");
        } else if (status.equals("zxStatus")) {
            sql.append(" order by h.zxStatus desc,h.zxDate");
        } else if (status.equals("rzStatus")) {
            sql.append(" order by h.rzStatus desc,h.rzDate");
        } else if (status.equals("bzStatus")) {
            sql.append(" order by h.bzStatus desc,h.bzDate");
        }
        return this.findHql(sql.toString(), map);
    }

    /**
     * 通过征收项目、安置项目、楼号得到所分配的houseList
     *
     * @param zsProjectId
     * @param azProjectId
     * @param buildNum
     * @return
     */
    public List<House> houseList(String zsProjectId, Integer azProjectId, String buildNum, String floorList) {

        StringBuffer hql = new StringBuffer("from House h where h.azProject.id = :azProjectId and h.buildNum = :buildNum ");
        hql.append("and h.id in (select zph.houseId from ZsProjectHouse zph where zph.zsProjectId in (" + zsProjectId + ")) ");
        if (floorList != null && !floorList.equals("")) {
            hql.append(" and h.floor in (" + floorList + ")) ");
        }
        hql.append(" order by h.floor,h.houseNum");
        Map<String, Object> map = Maps.newLinkedHashMap();
        map.put("azProjectId", azProjectId);
        map.put("buildNum", buildNum);
        return this.findHql(hql.toString(), map);
    }

    /**
     * 通过征收项目、安置项目、楼号得到所分配的houseList可选房源
     *
     * @param zsProjectId
     * @param azProjectId
     * @param buildNum
     * @return
     */
    public List<House> houseList(String zsProjectId, Integer azProjectId, String buildNum, String floorList, Integer houseStatus) {

        StringBuffer hql = new StringBuffer("from House h where h.azProject.id = :azProjectId and h.buildNum = :buildNum ");
        hql.append("and h.id in (select zph.houseId from ZsProjectHouse zph where zph.zsProjectId in (" + zsProjectId + ")) ");
        if (floorList != null && !floorList.equals("")) {
            hql.append(" and h.floor in (" + floorList + ")) ");
        }
        if (houseStatus != null && houseStatus == 1) {
            hql.append(" and h.houseStatus =1 ");
        }
        hql.append(" order by h.floor,h.houseNum");
        Map<String, Object> map = Maps.newLinkedHashMap();
        map.put("azProjectId", azProjectId);
        map.put("buildNum", buildNum);
        return this.findHql(hql.toString(), map);
    }

    /**
     * 通过安置项目Id 楼号 楼层 得到对应的房屋信息
     *
     * @param azProjectId
     * @param buildNum
     * @param floor
     * @return
     */
    public List<House> houseList(Integer azProjectId, String buildNum, Integer floor) {
        StringBuffer hql = new StringBuffer("from House h where h.azProject.id = :azProjectId and h.buildNum = :buildNum and h.floor = :floor ");
        hql.append("ORDER by h.houseNum,h.id ");
        Map<String, Object> map = Maps.newLinkedHashMap();
        map.put("azProjectId", azProjectId);
        map.put("buildNum", buildNum);
        map.put("floor", floor);
        return this.findHql(hql.toString(), map);
    }


    /**
     * 得到安置名称下所协议id
     *
     * @param azProjectName
     * @return
     */
    public List<Integer> getContractIdListByAzProjectName(String azProjectName, String startDate, String stopDate) {
        String sql = "select  h.contract_id  from house h inner join az_project azp on h.az_project_id=azp.id ";
        sql = sql + " where h.contract_id is not null ";
        if (azProjectName != null && !azProjectName.equals("")) {
            sql = sql + " and azp.project_name like  '%" + azProjectName + "%'";
        }
        if (startDate != null && startDate != "") {
            sql = sql + " and DATE (h.choose_house_date) >= " + "'" + startDate + "'";
        }
        if (stopDate != null && stopDate != "") {
            sql = sql + " and DATE(h.choose_house_date) <= " + "'" + stopDate + "'";
        }
        return this.entityManager.createNativeQuery(sql)
                .getResultList();
    }

    /**
     * 得到某一年内
     *
     * @param azProjectName
     * @return
     */
    public List<Integer> getHouseIdListByChooseHouseDateYear(String yearStr) {
        String sql = "select  DISTINCT(h.id)  from house h  ";
        sql = sql + " where h.choose_house_date is not null ";
        if (yearStr != null && !yearStr.equals("")) {
            sql = sql + " and YEAR(h.choose_house_date) = " + yearStr;
        }
        return this.entityManager.createNativeQuery(sql)
                .getResultList();
    }

    /**
     * 得到某一年内选房的征收项目Id
     *
     * @param azProjectName
     * @return
     */
    public List<Integer> getZspIdListByChooseHouseDateYear(String townCode, String yearStr, Integer azProjectId) {
        String sql = "select  DISTINCT(c.zs_project_id)  from house h inner join contract c on h.contract_id=c.id inner join az_project azp on h.az_project_id= azp.id  ";
        sql = sql + " where h.choose_house_date is not null ";
        if (StringUtils.isNotEmpty(townCode)) {
            sql = sql + " and azp.town = " + townCode;
        }
        if (StringUtils.isNotEmpty(yearStr)) {
            sql = sql + " and YEAR(h.choose_house_date) = " + yearStr;
        }
        if (azProjectId != null & azProjectId != 0) {
            sql = sql + " and azp.id = " + azProjectId;
        }
        return this.entityManager.createNativeQuery(sql)
                .getResultList();
    }

    /**
     * 查询某协议下所有的已选房源
     * 根据面积大小排序
     *
     * @param contractId
     * @return
     */
    public List<House> getHouseListByContractIdForBuildArea(Integer contractId) {
        StringBuffer hql = new StringBuffer("from House h inner join fetch h.azProject azp where h.contract.id = :contractId order by (h.propertyArea+h.poolArea)");
        List<House> houseList = entityManager.createQuery(hql.toString()).setParameter("contractId", contractId).getResultList();
        return houseList;
    }


    /**
     * 查询某协议下所有的非保障区已选房源
     * 根据面积大小排序
     *
     * @param contractId
     * @return
     */
    public List<House> getHouseListByContractIdForBuildAreaAndBz(Integer contractId, String houseStyle) {
        StringBuffer hql = new StringBuffer("from House h inner join fetch h.azProject azp where h.contract.id = :contractId and h.houseStyle = :houseStyle order by (h.propertyArea+h.poolArea)");
        List<House> houseList = entityManager.createQuery(hql.toString()).setParameter("contractId", contractId).setParameter("houseStyle", houseStyle).getResultList();
        return houseList;
    }


    /**
     * 查询某协议下所有的非保障区已选房源
     * 根据面积大小排序
     *
     * @param contractId
     * @return
     */
    public List<House> getHouseListByContractIdForBuildAreaAndNoBz(Integer contractId, String houseStyle) {
        StringBuffer hql = new StringBuffer("from House h inner join fetch h.azProject azp  ");
        hql = hql.append(" where h.contract.id = :contractId and h.houseStyle in ('普选房','保障转普选','期房')");
        hql = hql.append(" order by (h.propertyArea+h.poolArea))");
        List<House> houseList = entityManager.createQuery(hql.toString()).setParameter("contractId", contractId).getResultList();
        return houseList;
    }

    /**
     * 面积大于当前房源的面积list
     *
     * @param zsProjectId
     * @return
     */
    public List<BigDecimal> getBuildAreaIntListByHouseId(Contract contract, String houseHoldStr, BigDecimal area) {
        String sql = "SELECT FLOOR(h.property_area+h.pool_area) AS buildArea ";
        sql = sql + " FROM house h ";
        sql = sql + " WHERE h.id in (SELECT DISTINCT zph.house_id FROM zs_project_house zph where zph.zs_project_id =:zsProjectId) ";
        sql = sql + " AND h.house_status=1  AND (h.property_area+h.pool_area) > :houseArea ";
        if (StringUtils.isNotEmpty(houseHoldStr)) {
            sql = sql + " AND h.house_hold in (" + houseHoldStr + ") ";
        }
        sql = sql + " GROUP BY buildArea ORDER BY buildArea";
        Map<String, Object> map = Maps.newHashMap();
        map.put("zsProjectId", contract.getZsProject().getId());
        map.put("houseArea", area);
        return (List<BigDecimal>) this.findSqlObject(sql, map);
    }


    /**
     * 获得保障区可以选四房的面积大于当前房源的面积list
     *
     * @param zsProjectId
     * @return
     */
    public List<BigDecimal> getBzBuildAreaIntListByHouseId(Integer zsProjectId, Integer azProjectId, BigDecimal area) {
        String sql = "SELECT FLOOR(h.property_area+h.pool_area) AS buildArea ";
        sql = sql + " FROM house h ";
        sql = sql + " WHERE h.id in (SELECT DISTINCT zph.house_id FROM zs_project_house zph where zph.zs_project_id =:zsProjectId) ";
        sql = sql + " AND h.house_status=1  AND (h.property_area+h.pool_area) > :houseArea ";
        sql = sql + " AND h.house_hold = '0402' ";
        sql = sql + " and h.house_style = '保障区房源'  ";
        sql = sql + " and h.az_project_id = :azProjectId  ";
        sql = sql + " GROUP BY buildArea ORDER BY buildArea";
        Map<String, Object> map = Maps.newHashMap();
        map.put("zsProjectId", zsProjectId);
        map.put("houseArea", area);
        map.put("azProjectId", azProjectId);
        return (List<BigDecimal>) this.findSqlObject(sql, map);
    }

    /**
     * 面积大于当前房源的面积list
     *
     * @param zsProjectId
     * @return
     */
    public List<BigDecimal> getBuildAreaIntListByHouseIdAndChooseLimitMemo(Integer zsProjectId, Integer azProjectId, String houseHoldStr, BigDecimal area) {
        String sql = "SELECT FLOOR(h.property_area+h.pool_area) AS buildArea ";
        sql = sql + " FROM house h ";
        sql = sql + " WHERE h.id in (SELECT DISTINCT zph.house_id FROM zs_project_house zph where zph.zs_project_id =:zsProjectId) ";
        sql = sql + " AND h.house_status=1  AND (h.property_area+h.pool_area) > :houseArea ";
        //当前可选户型不为空
        if (StringUtils.isNotEmpty(houseHoldStr)) {
            //二房
            if (houseHoldStr.equals("0202")) {
                sql = sql + " AND h.house_hold = '0202' ";
            } else if (houseHoldStr.equals("0302")) {
                //三房二厅
                sql = sql + " AND h.house_hold = '0302' ";
            } else if (houseHoldStr.equals("0402")) {
                sql = sql + " AND h.house_hold = '0402' ";
            }
        }
        sql = sql + " and h.house_style in  ('普区房','期房','保障转普选' )  ";
        if (azProjectId != null) {
            sql = sql + " and h.az_project_id = :azProjectId  ";
        }
        sql = sql + " GROUP BY buildArea ORDER BY buildArea";
        Map<String, Object> map = Maps.newHashMap();
        map.put("zsProjectId", zsProjectId);
        map.put("houseArea", area);
        if (azProjectId != null) {
            map.put("azProjectId", azProjectId);
        }
        return (List<BigDecimal>) this.findSqlObject(sql, map);
    }


    /**
     * 得到houseIdList获取Hosue
     *
     * @param contractIdList
     * @return
     */
    public List<House> getHouseByHouseIdList(List<Integer> houseIdList) {
        StringBuffer hql = new StringBuffer("from House h  where h.id in (:houseIdList)  ");
        List<House> houseList = entityManager.createQuery(hql.toString()).setParameter("houseIdList", houseIdList).getResultList();
        return houseList;
    }

    /**
     * 根据征收项目所分配房源的每个户型所对应的房源，并封装map
     *
     * @param zsProjectId
     * @return
     */
    public Map<String, Integer> getHouseMapByHouseHoldAndZsprojectId(Integer zsProjectId) {
        StringBuffer sql = new StringBuffer(" SELECT h.house_hold as houseHold, ");
        sql.append(" IFNULL(COUNT(h.id),0) AS houseCount ");
        sql.append(" FROM house h WHERE h.house_status=1 and h.house_style !='保障区房源' and h.id in (select zph.house_id from zs_project_house zph where zph.zs_project_id = :zsProjectId) GROUP BY h.house_hold ");
        Query query = this.entityManager.createNativeQuery(sql.toString()).setParameter("zsProjectId", zsProjectId);
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Integer> resultMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String houseHold = map.get("houseHold").toString();
                Integer houseCount = Integer.valueOf(map.get("houseCount").toString());
                resultMap.put(houseHold, houseCount);
            }
        }
        return resultMap;
    }


    /**
     * 得到houseList
     *
     * @param houseList
     * @return
     */
    public List<House> getHouseListByHouseMsg(Integer zsProjectId, String houseHold) {

        StringBuffer hql = new StringBuffer(" from House h  where h.houseStatus=1 and h.houseHold = :houseHold  and h.houseStyle='保障房' and h.lzNum IS NOT NULL ");
        hql.append(" and h.id in (select zph.houseId from ZsProjectHouse zph where zph.zsProjectId =:zsProjectId) order by h.lzNum");
        List<House> houseList = entityManager.createQuery(hql.toString()).setParameter("houseHold", houseHold).setParameter("zsProjectId", zsProjectId).getResultList();

        return houseList;
    }


    /**
     * 根据房源流转顺序更改房源选房时所在区域性质
     *
     * @param house
     * @return
     */
    @Transactional
    public void setHouselzNum(Contract contract) throws IllegalAccessException, NoSuchMethodException, InvocationTargetException {
        List<House> bzChooseHouseList = this.getHouseListByContractIdForBuildAreaAndBz(contract.getId(), "保障房");
        int bzChooseNum0302 = 0;
        int bzChooseNum0402 = 0;
        if (CollectionUtils.isNotEmpty(bzChooseHouseList)) {
            for (House house : bzChooseHouseList) {
                if (house.getHouseHold().equals("0402")) {
                    bzChooseNum0402 = bzChooseNum0402 + 1;
                } else if (house.getHouseHold().equals("0302")) {
                    bzChooseNum0302 = bzChooseNum0302 + 1;
                }
            }
        }
        //判断并且循环安排所需户型，并根据实际已选情况判断剩余可选保障房情况户型套数
        if (StringUtils.isNotEmpty(contract.getBaoZhangType())) {
            List<String> baoZhangTypeList = Arrays.asList(contract.getBaoZhangType().split(";"));
            for (String s : baoZhangTypeList) {
                if (s.contains("可保障三房二厅")) {
                    if (s.contains("1")) {
                        if (bzChooseNum0302 == 0) {
                            //该情况下该户未选择保障房三房二厅，因此需要有一套三房流转
                            this.saveHouseAndContractForLz(contract, "0302");
                        } else if (bzChooseNum0302 == 1) {
                            //该情况下该户已选择保障房三房二厅一套，无需流转
                        }
                    } else if (s.contains("2")) {
                        if (bzChooseNum0302 == 0) {
                            for (int i = 1; i <= 2; i++) {
                                this.saveHouseAndContractForLz(contract, "0302");
                                //该户未选择保障房三房二厅，因此需要流转二套三房
                            }
                        } else if (bzChooseNum0302 == 1) {
                            this.saveHouseAndContractForLz(contract, "0302");
                            //该情况下该户已选择保障房三房二厅一套，因此需要有一套三房流转
                        } else if (bzChooseNum0302 == 2) {
                            //该情况下该户已选择保障房三房二厅二套，无需流转
                        }
                    }
                } else if (s.contains("可保障四房二厅")) {
                    if (s.contains("1")) {
                        if (bzChooseNum0402 == 0) {
                            //该情况下该户未选择保障房四房二厅，因此需要有一套四房流转
                            this.saveHouseAndContractForLz(contract, "0402");
                        } else if (bzChooseNum0402 == 1) {
                            //该情况下该户已选择保障房四房二厅一套，无需流转
                        }
                    } else if (s.contains("2")) {
                        if (bzChooseNum0402 == 0) {
                            //该情况下该户未选择保障房四房二厅二套，需要流转二套四房
                            for (int i = 1; i <= 2; i++) {
                                this.saveHouseAndContractForLz(contract, "0402");
                            }
                        } else if (bzChooseNum0402 == 1) {
                            //该情况下该户已选择保障房四房二厅一套，因此需要有一套四房流转
                            this.saveHouseAndContractForLz(contract, "0402");
                        } else if (bzChooseNum0402 == 2) {
                            //该情况下该户已选择保障房四房二厅二套，无需流转
                        }
                    } else if (s.contains("3")) {
                        if (bzChooseNum0402 == 0) {
                            //该情况下该户未选择保障房四房二厅三套，因此需要三套四房流转
                            for (int i = 1; i <= 3; i++) {
                                this.saveHouseAndContractForLz(contract, "0402");
                            }
                        } else if (bzChooseNum0402 == 1) {
                            //该情况下该户已选择保障房四房二厅一套，需要流转二套四房
                            for (int i = 1; i <= 2; i++) {
                                this.saveHouseAndContractForLz(contract, "0402");
                            }
                        } else if (bzChooseNum0402 == 2) {
                            //该情况下该户已选择保障房四房二厅二套，因此需要有一套四房流转
                            this.saveHouseAndContractForLz(contract, "0402");
                        } else if (bzChooseNum0402 == 3) {
                            //该情况下该户已选择保障房四房二厅三套，无需流转
                        }
                    }
                }
            }
        }
    }

    public void saveHouseAndContractForLz(Contract contract, String houseHold) {
        House house = this.getHouseListByHouseMsg(contract.getZsProject().getId(), houseHold).get(0);
        house.setHouseStyle("保障转普选");
        house.setLzNumMemmo(DateUtils.dateToStringDefault(contract.getChooseHouseDate()) + "该房为选房号：" + contract.getChooseHouseNum() + ",被征收人：" + contract.getName() + "放弃“保障房”" + houseHold + "房源转为普选区房源");
        this.save(house);
        contract.setBaoZhangType(contract.getBaoZhangType() + "已放弃选择“保障房”" + houseHold + "房源，且流转顺序为" + house.getLzNum() + "的" + house.getAzProject().getProjectName() + house.getBuildNum() + "号楼" + house.getHouseNum() + "被流转为保障转普选房源;");
        this.contractService.save(contract);
    }

    /**
     * 协议下房屋审核情况list
     *
     * @param contractId
     * @return
     */
    public List<String> getHouseDeedShStatusList(Integer contractId) {
        StringBuffer sql = new StringBuffer("select distinct h.deed_sh_status as deedShStatus from house h ");
        sql.append(" where h.contract_id =:contractId ");
        List<String> getHouseDeedShStatusList = (List<String>) this.entityManager.createNativeQuery(sql.toString()).setParameter("contractId", contractId).getResultList();
        return getHouseDeedShStatusList;
    }


    /**
     * 得到安置名称下所协议id
     *
     * @param azProjectName
     * @return
     */
    public List<Integer> getAzprojectIdListByTown(String town) {
        String sql = "select  distinct(h.az_project_id)  from house h inner join contract c on h.contract_id=c.id inner join zs_project zsp on c.zs_project_id= zsp.id";
        sql = sql + " where zsp.town = '" + town + "'";

        return this.entityManager.createNativeQuery(sql)
                .getResultList();
    }

    /**
     * 通过协议Id得到对应得到房源列表
     * @param contractId
     * @return
     */
    public List<House> getHouseListByConId(Integer contractId) {
        StringBuffer hql = new StringBuffer(" from House h  where h.contract.id = :contractId ");
        hql.append(" ORDER BY IFNULL(h.propertyArea,0) + IFNULL(h.poolArea,0) DESC ");
        List<House> houseList = entityManager.createQuery(hql.toString()).setParameter("contractId", contractId).getResultList();
        return houseList;
    }

    /**
     * 计算安置区房源对应的安置面积
     * @param contract
     */
    public void calAzAreaForHouse(Contract contract) {
        List<House> houseList = this.getHouseListByConId(contract.getId());
        if (CollectionUtils.isNotEmpty(houseList)) {
            BigDecimal houseAreaNoLast = BigDecimal.ZERO;//不含最后一套面积
            for (int i=0;i < houseList.size();i++) {
                if (contract.getIsJiSuan() != 1) {//若属于合并以及期房的房源：房源面积即是回迁安置面积
                    houseList.get(i).setHqAzArea(houseList.get(i).getBuildArea());
                    this.save(houseList.get(i));
                } else {
                    if (i != houseList.size() - 1) {//不是最后一次循环
                        houseAreaNoLast = BigDecimalUtils.getSum(houseAreaNoLast,houseList.get(i).getBuildArea());//不含最后一套面积
                        houseList.get(i).setHqAzArea(houseList.get(i).getBuildArea());
                    } else {//最后一次循环 剩余面积即安置面积
                        houseList.get(i).setHqAzArea(BigDecimalUtils.getBigAndBigSub(contract.getLimitHouseAreaTotal(),houseAreaNoLast));
                    }
                    this.save(houseList.get(i));
                }
            }
        }
    }

    /**
     * 单栋公维金单价获取
     * @param maxFloor
     * @return
     */
    public BigDecimal getGwjPrice(int maxFloor) {
        BigDecimal gwjPrice = BigDecimal.ZERO;
        if (maxFloor <= 10) {
            gwjPrice = new BigDecimal("40");
        } else if (maxFloor <= 20) {
            gwjPrice = new BigDecimal("50");
        } else if (maxFloor <= 35) {
            gwjPrice = new BigDecimal("70");
        } else if (maxFloor > 35) {
            gwjPrice = new BigDecimal("110");
        }
        return gwjPrice;
    }

    /**
     * 得到楼栋的最高层
     * @param azProjectId
     * @param buildNum
     * @return
     */
    public Integer getMaxFloor(Integer azProjectId, String buildNum) {
        StringBuffer sql = new StringBuffer("select IFNULL(MAX(h.floor),0) as 'maxFloor' from House h");
        sql.append(" where h.az_project_id =:azProjectId ");
        sql.append(" and h.build_num =:buildNum ");
        Map<String, Object> map = Maps.newLinkedHashMap();
        map.put("azProjectId", azProjectId);
        map.put("buildNum", buildNum);
        return Integer.valueOf(String.valueOf(this.findSqlObject(sql.toString(), map).get(0)));
    }
}
